I need something to count (two digits 00-99) per date and by per user.

So, if I enter a quote I need that quote for today to be 00 and the next one to be 01 up until 99.

BUT if another user enters quotes on the same day, the count should restart with 00 for the new user.

I need this in a query as well as in a form.

So far I was able to get help with:
field named txtCountUserDate = "01"
but it does not increment nor does it restart the count with a new user.

The QuoteNumber needs to be:
yymmdd##@@

1. "yymmdd" is driven from the EntryDate in the Quotes table - Format([EntryDate),"yymmdd"
2. COUNT (##) is what I need to automatically start at 00 for each new user on the same day and also restart for the same user on the next day.
3. UserID (@@) is driven from the FirstName and LastName from the Employee table. Left([FirstName],1) & [Left([LastName])

I do have a QuoteID that is set to AutoNumber and auto increment but this does not restart and is continues. So I can't use this for the "QuoteNumber".

They need the QuoteNumber to be the logic mentioned above.

Example:


060724 01 AE
yymmdd count user initials