incrementing number based on criteria

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

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


"Gary" wrote in message
...
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

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.

--
HTH
Van T. Dinh
MVP (Access)



"Gary" wrote in message
...
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