incrementing number based on 2 other fields

[gmmccarthy]

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.

I asked this earlier but thought I would restate my question in hopes of getting my thoughts around this. I appreciate your patience with me.

Thanks.

Gary

 

[gmmccarthy]

This is similar to another response I've had but maybe I just don't understand what's happening in the line:

CInt(Nz(DMax("Right(Yourkey,3)", "YourTable", "Left(YourKey, 3) = 'XYZ'"),0))

I'd appreciate anyone breaking this down for me (like what is Nz?) and maybe that will help me with my problem.

Thanks.

 

[ShaneMan]

Hey Gary,

I'll at least help throw some thoughts your way. Can't say they'll be good ones but I know you've posted twice on this with little to no takers so I thought I would at least attempt to lend ya a hand.

I would think you could, through code, check some where along the way for NewRecord. When you've established that it's new then getting today's date will be easy, Date(), so you have the date part of your number. Getting the initials I would think would be easy for you to get, so you have 2 parts of your number. The third part I would think that you could do a recordcount based off of today's date and the user's initials, for the criteria and after you have the recordcount you could add 1 to it. Once you have all 3 pieces or information, then you could concatenate it all.

Does that help any?
Shane

P.S. Nz is coverting Null. In the example you posted it's being turned into 0

 

[gmmccarthy]

Hi,

I can get this to work in a totals query but not on a form.

I have this for the control source:

=DCount("[initials]","pickupreqdatafrm","[reqdate]=me.reqdate AND initials =me.initials")+1

Do I have the format wrong? I'm looking to return the count value and then concatenate the 3 parts of the Ref#.

Thanks.

Gary