Format an AutoNumber in Access

[SelfLearner]

I am trying to have an AutoNumber automatically saved when data is entered into a table. The format is a little different but what I am trying to do is have a specific number auto filled out. This may be impossible to do but I thought I would give it a try. This is what I would like to happen in the AutoNumber field. I want the first part of the serial number to be "W91FYE4" Then I need the Julian Date (Only the day NOT year) after the 4, followed by an "O" Then followed by a 3 digit AutoNumber starting at 100. The really difficult part is resetting the 3 digit AutoNumber back to 100 everyday. The following example shows what I need it to look like. x's are the AutoNumber fields. W91FYE4XXX0XXX. The first 3 X's are the Julian Date Day and the second three X's are AutoNumbered starting at 100 and resetting back to 100 each day.

[Dal Jeanis]

1) Generally, you should store those pieces separately, since it makes it easier to do everything you are asking for. If you want, you can also store the reformatted field, but most folks on the forum would tell you not to store a value that can be calculated.

2) Use an integer field for your 3-digit "autonumber" counter.

3) DO store the entire year in the serial-number-date field, or you'll kill yourself for no reason. Just ignore the year when you build the display format.

4) If you keep the year it's not hard to reset to 1 at the beginning of the day, it's automatic. Search for the MAX counter for the current date. If NULL, then use 1, otherwise add 1 to the max. Special code - if the next counter value reaches 1000, you'll have to add 1 to the date.


You can google for "Custom Autonumber" or "Custom Counter" to get various discussions and other solutions. Here's three useful pages to get you started.

http://support.microsoft.com/kb/210194
http://msgroups.net/microsoft.public...onumber/133479
http://www.office-archive.com/3-ms-a...a062aaa733.htm

Hopefully, this should be enough info for you to mark the thread solved.