Autonumbering

[Pat]

Is there a way to create an autonumber by year, month,
day, then sequential number for that day? Something like
YYYYMMDD###

I'm wanting to "try" and build a database to login lab
samples and have Access automatically generate a
laboratory ID number like 20040916001.

Thanks in advance for any help!

 

[Ken Snell [MVP]]

Do not combine the data values this way in a single field! While it'll look
nice, it will require more complex queries if you want to find information
from that field that is just part of that field's value. Instead, use
separate fields for the date and the autonumber, then use concatenation to
display the values together in reports, forms, and queries.

TableName
fldSequence (autonumber field)
fldDate (date/time field)

Then just concatenate the fields' values to show them together:
Format(fldDate, "YYYYMMDD") & Format(fldSequence, "000")

Note that this setup will have unique sequence values for each record. If
you want to start at 1 again with each new date, then you'll need to use an
expression in a form as the Default Value of the control that is bound to
the fldSequence field (which now must be a Long Integer field, not an
autonumber field) when the new record is being created. The expression would
"read" the table to see what the current highest value of fldSequence is for
that date and then add one to it. The default value expression (note: this
won't work in the table's Default Value property) would be something like
this:
= Nz(DMax("fldSequence", "TableName", "fldDate = " & Date()), 0) + 1

 

[Pat]

Thanks for the advise and examples!!

-----Original Message-----
Do not combine the data values this way in a single field! While it'll look
nice, it will require more complex queries if you want to find information
from that field that is just part of that field's value. Instead, use
separate fields for the date and the autonumber, then use concatenation to
display the values together in reports, forms, and queries.

TableName
fldSequence (autonumber field)
fldDate (date/time field)

Then just concatenate the fields' values to show them together:
Format(fldDate, "YYYYMMDD") & Format (fldSequence, "000")

Note that this setup will have unique sequence values for each record. If
you want to start at 1 again with each new date, then you'll need to use an
expression in a form as the Default Value of the control that is bound to
the fldSequence field (which now must be a Long Integer field, not an
autonumber field) when the new record is being created. The expression would
"read" the table to see what the current highest value of fldSequence is for
that date and then add one to it. The default value expression (note: this
won't work in the table's Default Value property) would be something like
this:
= Nz(DMax("fldSequence", "TableName", "fldDate = " & Date()), 0) + 1

--

Ken Snell
<MS ACCESS MVP>




.