incrementing number based on criteria

[Gary]

I want my db to generate our reference # automatically based on input from
other fields. The number is the date record entered, the users initials, and
an incremental number. Ex. 120505GMM02

My form is based on a query and I have the query generating the first two
components from the date entered in reqdate and the initials entered. How can
I get an incremental number generated to go with the rest.

The number should start with 01 for each day and also for each user.
Therefore when GMM is entering his third record on the 5th it should generate
120505GMM03 even if there were 10 other records entered on the fifth by other
users. Each users numbers would start at 01 for each day.

Thanks.

Gary

 

[tina]

use a DCount() function to count the number of records already in the table
for that user on that day, and add 1. see the DCount() Function topic in
Help to understand how it works, especially the criteria argument of the
function.

hth

 

[Van T. Dinh]

In addition to what Tina wrote, you should store your Ref# as 3 Fields, one
for the date of entry, one for the creator and one for the sequence number,
NOT as one Field.