trying to find proper format

[sheltonr2]

Hello all,
I am trying to figure out how to create an field that will display two set characters (AA) followed by the current two digit year (YY) current two digit month (MM) then a sequential 3 digit auto number (NNN). Final product being AAYYMMNNN. For example for this month it would appear AA0906001, the next entry would be AA0906002 and so on.

I'm rater new to access so any help is appreciated.

Warm regards,
Randolph

 

[pbaldy]

Welcome to the site. I assume you want the sequential number to start at 1 again in July? If the AA is set, I wouldn't bother saving it. You can add it for display. Presuming your data has a date field, I also wouldn't save the date parts separately. For the sequential number, you can use a DMax() to find the highest sequential value for the current month, and add 1 to it. Use the Nz() function to handle the first one of a month. On forms and reports, a textbox control source of:

="AA" & Format(DateField, "YYMM") & Format(SequentialNumber, "000")

Play around with all that and see how it works.

 

[Atomic Shrimp]

This is almost exactly the same question as here:
http://www.access-programmers.co.uk/forums/showthread.php?t=173336

How peculiar.