Auto Number ID Field

[Toni]

Newbie in need of help!! Using an ID that is made up of w/ 2 digit year
plus 3-digit sequential number. 1st record for 2008 would start 08-001, then
08-002. In 2009 it would start over 09-001, 09-002. Trying to setup in
access database. I got it to work using auto number w/ format, but don't
know how I will be able to change when new year starts. (It changes all the
previous records from 08 to 09 when I change the format.) Also if I use
auto number for primary key then I can't use auto number again in same table,
I think. Also each ID # must be accounted for so no deletes. Thanks.

 

[Tom Wickerath]

Hi Toni,

Try this sample out
http://www.rogersaccesslibrary.com/...?TID=395&SID=fad5b6f428a78f17f8ba98956d3z56fa


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[BruceM]

Roger's code does not contain a provision for starting over (at least it
didn't last I checked). However, it can be readily modified to do so.
Assuming there is a date field in the record, you would do something like
this as the Default Value (see the sample database for the basic procedure):
=Nz(DMax("[YourField]","[YourTable]","Year([DateField]) = " &
Year([DateField]),0) + 1

Look at Roger's example, and add the above (using your actual table and
field names) in the Default Value of a text box bound to YourField just as
Roger has done.

That is all on one line. Assumptions: DateField and YourField are in
YourTable, which is the form's Record Source. YourField is a number field
that holds the incrementing number The idea is that you are telling Access
to look for the maximum value in YourField in which the year from DateField
is the same as the year from DateField in the current record. Nz takes care
of the first record of the year, when there is no maximum value in YourField
for that year. See Help for more information about the functions Nz, DMax,
and Year.
To display the number, combine it with the year from the DateField, and add
some formatting. One way is to set the Control Source of an unbound text
box to:
= Format([DateField],"yy") & "-" & Format([YourField],"000")

Note that you are not storing the entire number, but rather just the
incrementing part of it (which you have showed to the right of the hyphen).
Since the field restarts the numbering every year you cannot use it as the
primary key. It is possible, but more complex, to store in a single field a
text value consisting of the two-digit year, a hyphen, and the incremented
number, but it gains you nothing that I can see. Some will say it is
redundant in that you have already stored the year in DateField. I have to
say that argument does not completely convince me in this situation, but in
any case I would opt for the simpler choice.

 

[Toni]

thanks for your help.

Roger's code does not contain a provision for starting over (at least it
didn't last I checked). However, it can be readily modified to do so.
Assuming there is a date field in the record, you would do something like
this as the Default Value (see the sample database for the basic procedure):
=Nz(DMax("[YourField]","[YourTable]","Year([DateField]) = " &
Year([DateField]),0) + 1

Look at Roger's example, and add the above (using your actual table and
field names) in the Default Value of a text box bound to YourField just as
Roger has done.

That is all on one line. Assumptions: DateField and YourField are in
YourTable, which is the form's Record Source. YourField is a number field
that holds the incrementing number The idea is that you are telling Access
to look for the maximum value in YourField in which the year from DateField
is the same as the year from DateField in the current record. Nz takes care
of the first record of the year, when there is no maximum value in YourField
for that year. See Help for more information about the functions Nz, DMax,
and Year.
To display the number, combine it with the year from the DateField, and add
some formatting. One way is to set the Control Source of an unbound text
box to:
= Format([DateField],"yy") & "-" & Format([YourField],"000")

Note that you are not storing the entire number, but rather just the
incrementing part of it (which you have showed to the right of the hyphen).
Since the field restarts the numbering every year you cannot use it as the
primary key. It is possible, but more complex, to store in a single field a
text value consisting of the two-digit year, a hyphen, and the incremented
number, but it gains you nothing that I can see. Some will say it is
redundant in that you have already stored the year in DateField. I have to
say that argument does not completely convince me in this situation, but in
any case I would opt for the simpler choice.

Hi Toni,

Try this sample out:
http://www.rogersaccesslibrary.com/...?TID=395&SID=fad5b6f428a78f17f8ba98956d3z56fa


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________

 

[Toni]

thanks for your help.

Hi Toni,

Try this sample out:
http://www.rogersaccesslibrary.com/...?TID=395&SID=fad5b6f428a78f17f8ba98956d3z56fa


Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
http://www.access.qbuilt.com/html/expert_contributors.html
__________________________________________