AutoNumber

[michalj via AccessMonster.com]

Hi,

how can I create AutoNumber in format YYMMDDXXXX (year,month,day,sequence
number), where XXXX starts by 0001 every day.
e.g. first sequence number at 10feb09 should be 0902100001. next 0902100002,
etc,...,next day first one 0902110001, next one 0902110002, etc...

Thnx
Michal

 

[Jeff Boyce]

Michal

Perhaps not important, but when you say "Autonumber" and "Access" in the
same description, most folks will think you mean the Autonumber data type
Access provides. ... and you can't do what you described with the Access
Autonumber.

Next point, good data design precludes stuffing more than one fact into a
single field, and you appear to be trying to stuff a date and a sequence
number in the same field.

As an alternate approach, store a date in one (date/time) field, and store a
sequence number in another field, then use a query (or your form) to
concatenate them together in the format you described.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Order2Chaos]

I have a form that uses a similar approach to make a reference number (I
didn't decide on what to use, I just made it work). I use the following piece
of VB code "on current" for the form. This may not be suited for your needs,
but it might help.

TempNow = Format(Now(), "yyyymmdd")
TempAdditional = 1 + DCount("[tracking_number]", "[Local Quote
Database]", "left([tracking_number],8) = " & "'" & TempNow & "'")
TempLeading = IIf(TempAdditional > 9, "0", IIf(TempAdditional > 99, "",
"00"))
[Forms]![Local Quote]![tracking_number] = TempNow & "-" & TempLeading &
TempAdditional & "A"

This counts how often the first 8 digits of the current date, in yyyymmdd
format, appears in a table (which ignores the following "autonumber") and
adds 1 to it. It then works out how many leading 0's are required and slaps
it all together in a form field.

 

[michalj via AccessMonster.com]

I have a form that uses a similar approach to make a reference number (I
didn't decide on what to use, I just made it work). I use the following piece
of VB code "on current" for the form. This may not be suited for your needs,
but it might help.

TempNow = Format(Now(), "yyyymmdd")
TempAdditional = 1 + DCount("[tracking_number]", "[Local Quote
Database]", "left([tracking_number],8) = " & "'" & TempNow & "'")
TempLeading = IIf(TempAdditional > 9, "0", IIf(TempAdditional > 99, "",
"00"))
[Forms]![Local Quote]![tracking_number] = TempNow & "-" & TempLeading &
TempAdditional & "A"

This counts how often the first 8 digits of the current date, in yyyymmdd
format, appears in a table (which ignores the following "autonumber") and
adds 1 to it. It then works out how many leading 0's are required and slaps
it all together in a form field.

Hi,

[quoted text clipped - 5 lines]

Thnx
Michal

Hi All,

thnx for yr advise.
Sorry mayber forgot to explain some details. This "AutoNumber" should be use
as unique id number on our Service Call ("Issue") Database.
I`m not a programmer and totally new in access, so the tables and the
concatenate on the form looks for me simpler.
Can I ask you, to explain me step by step (for beginner), how to create this?.
..to get final unique number YYMMDDXXXX on every "Issue" form and "List of
issues" form? Is it ok to concatenate this within the "Issue Table" and then
"display" it on forms?

Thnx
Michal