auto sequence number for reference number

[gmmccarthy]

The title probably doesn't give an idea of what I need, so here goes.

We currently have reference #'s for our bills that include the date requested in mmddyy format, the first 3 letters of the customer, the initials of the order taker and finally a sequential number to show the sumber of bills that day.

example: 120705SEAGMM02 2nd order taken by GMM for Seagate on 12/7
120705SEARLH01 1st order taken by RLH for Seagate on 12/7

I want to generate this number automatically based on the date entered and the initials given of the user. We only deal with one customer at our desk so that will always be "SEA".

I have a query that generates the first portion (date, customer, and initials):

SELECT Format([REQDATE],"mm") AS [Month], Format([REQDATE],"dd") AS [Day], Format([REQDATE],"yy") AS [Year], Format([REQDATE],"mmddyy") AS [Both], [Both] & "SEA" & [PickUpReqData]![INITIALS] AS REFNO
FROM PickUpReqData;

But I can't seem to get my head around the part of generating the number. I know I had done this in a database I created 2 jobs ago but all my files were flooded out in Katrina. (I have since relocated to Atlanta, although I'm not looking for sympathy. But I will take what I can get!)

Anything to point me in the right direction will be appreciated. I am looking to have this on a form and feed the REFNO field in the PickUpReqData table.

Thanks.

Gary

 

[ScottGem]

The way I do this is to add a field called Increment. I then use the Before Insert event to set the value for a hidden control on my form bound to that field to:

=Nz(DMax("[Increment]","table","[ReqDate] = #" & Me.txtReqDate & "#"),0)+1

As for the Reference number, this should NOT be stored but simply concatenated for display since you are storing all the components.

=Format([ReqDate],"mmddyy") & Left([Customer],3) & Initials & Format([Increment],"00")

 

[gmmccarthy]

Scott,

Thanks a lot. But, also, is there a way to start the increment again and follow it for each user. Say, when GMM put in his third for the day it would give an 03 rather than, say, a 10 for the 1oth total order for the day?

Thanks.

 

[ScottGem]

yep, you would have to use the Criteria in the DMax to select the Max for both the date and user.