Working with 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.

 

[Michel Walsh]

Hi,

As a computed column in a query open on your table:

SerialNumber: 1000*Year( TheDate) + DCount("*", "TableNameHere",
"Year(TheDate)=" & Year(THeDate) & " AND TheDate>=" & Format(TheDate,
"\#mm-dd-yyyy\#") )



Note that this will produce 2003001, not 03001, since a number cannot
start with a leading zero (unless its magnitude is less than one). Sure, you
can subtract 2000000 from it, but then, you get 3001, not 03001. To get a
leading 0, you have to use a string, rather than a number.


SerialNumberString: Format( 1000*Year( TheDate) + DCount("*",
"TableNameHere", "Year(TheDate)=" & Year(THeDate) & " AND TheDate>=" &
Format(TheDate, "\#mm-dd-yyyy\#") ) - 2000000, "00000")





Vanderghast, Access MVP