Problems creating a reference number

[Steveb]

Hi

I have a database where I need to give each record an individual reference
number which ideally would consist of the year and the record number. For
example, the first record in 2006 would be 2006-001, the second 2006-002 and
then the first entry in 2007 would be 2007-001. I have a field showing the
date a record was entered (Date_Entered) which I can use for the year.

Any help on this would be much appreciated.

SteveB

 

[Guest]

Hey Steve,

If you have a multiuser database, you will need to watch out for concurrency
issues with two people getting a new number at the same time, but the easiest
way would be something like this:

NewNumber = dcount("*", "[Table Needing Individual Ref Numbers]",
"Year([EntryDate]) = " & year(date())) + 1

That will get you the number for a given year.

If concurrency is an issue, you might then like to add a check that the
returned number doesn't exist before saving the record.

Hope this helps.

Damian.