Working on numbers

[CLey]

Pls. help! I need to generate an ID system based on the
date entered.

Ex. If I enter a date 11/20/03, the result ID should be:

03001 - where the first 2 digits are the year of the
date, and the last 3 digits are the first record entered
on that year. therefore the next record whose date is
12/01/03, should have the ID number below:

03002 - based on year 2003, second record.

If the date is 01/03/04, the number should be:

04001 - for year 2004, first record.

any help will be much appreciated. Thank you.

 

[Allen Browne]

Since you have a leading zero, your ID field must be of type Text, so the
new ID value will be something like this:

Format(Val(Nz(DMax("ID", "MyTable", "ID Like """ & Right(Year(Date()),2) &
"*"""), Right(Year(Date()),2) & "000")) + 1), "00000")

Note that it would be simpler to use two numeric fields: one for the year,
and the other for counter within that year. You can still output them in the
desired format:
=Right([MyYear],2) & Format([MyCount], "000")