Autonumber for Report   

Thanks in advance for any help someone may be able to offer.

I would like to create a three number field that increments by one digit but 
is preceeded by the last two digits of the current year and a hyphen, such as 
08-001, 08-002, 08-003.  We would fill out individual reports throughout 2008 
and then in 2009 the format would automatically change to 09-###, 09-###+1, 
etc.

The primary key is set to NCRID and the field I want to create the 08-### 
format is called ReportNumber.

I would be happy to hear if anyone has different suggestions on how to create

Here are a few fields of my table...

tblNonConformanceReport
NCRID
ReportNumber
Customer
etc...

Thank You

date: Wed, 2 Jul 2008 11:32:20 -0700   author:   Stimpy707

Re: Autonumber for Report   

So, although you are using the term "report", you aren't necessarily 
referring to what Access considers a "report", right?

You can add a field that holds a sequence number.  Then you'd use a query to 
concatenate the last two characters of the record's date/time 'year' with a 
hyphen and the sequence number, formatted as "00#".  No need to store all 
that, but you will need the sequence number and the date/time value.

Check mvps.org/access for "Custom Autonumber" to get some ideas of how to 
build something like this.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Stimpy707"  wrote in message 
news:1B510D52-FCE2-4FBF-BE18-B9DEDF29F595@microsoft.com...
> Thanks in advance for any help someone may be able to offer.
>
> I would like to create a three number field that increments by one digit 
> but
> is preceeded by the last two digits of the current year and a hyphen, such 
> as
> 08-001, 08-002, 08-003.  We would fill out individual reports throughout 
> 2008
> and then in 2009 the format would automatically change to 09-###, 
> 09-###+1,
> etc.
>
> The primary key is set to NCRID and the field I want to create the 08-###
> format is called ReportNumber.
>
> I would be happy to hear if anyone has different suggestions on how to 
> create
>
> Here are a few fields of my table...
>
> tblNonConformanceReport
> NCRID
> ReportNumber
> Customer
> etc...
>
> Thank You

date: Wed, 2 Jul 2008 12:01:51 -0700   author:   Jeff Boyce

RE: Autonumber for Report   

You need an additional field for report year.  
To get the next available number you can use:

 = Nz(DMax("NCRID","tblNonConformanceReport","ReportYear = " & Year(Date)), 
0) + 1

Now, there can be one problem with this method depending on how many users 
may be creating reports at the same time.  It is possible for one user to get 
the number and be entering data for the report, but before she saves it, 
another user starting a report will get the same number.  You have to allow 
for that possibility.  How you resolve it depends on the likelyhood of it 
happening and whether you use the report number as part of a unique index.


-- 
Dave Hargis, Microsoft Access MVP


"Stimpy707" wrote:

> Thanks in advance for any help someone may be able to offer.
> 
> I would like to create a three number field that increments by one digit but 
> is preceeded by the last two digits of the current year and a hyphen, such as 
> 08-001, 08-002, 08-003.  We would fill out individual reports throughout 2008 
> and then in 2009 the format would automatically change to 09-###, 09-###+1, 
> etc.
> 
> The primary key is set to NCRID and the field I want to create the 08-### 
> format is called ReportNumber.
> 
> I would be happy to hear if anyone has different suggestions on how to create
> 
> Here are a few fields of my table...
> 
> tblNonConformanceReport
> NCRID
> ReportNumber
> Customer
> etc...
> 
> Thank You

date: Wed, 2 Jul 2008 12:18:00 -0700   author:   Klatuu