Autonumber for Report

[Stimpy707]

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

 

[Jeff Boyce]

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

 

[Klatuu]

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.