MS Access 2003 (Create an Alphanumeric Autonumbered Field)

[slblakley]

I need to create an autonumbered field in my access 2003 database that
looks like the following:
DCR-07-000

The DCR is a constant, the 07 is the year and will need to update as
the year changes and the 000 is sequential.

Any help would be awesome!! Thanks so much!!!

 

[Guest]

It is bad database design to put more than one piece of information in a
single field. In your case you want to put a year and a sequence number in
the same field, don't!

If the DCR is a constant, leave it out. If you want to display this on a
form, it is easy to do.

Create a separate field for the year, and another for your sequence number
(I'll call this Seq_No). If Seq_No needs to start over at 001 each year,
then it needs to be an integer or long integer. If it doesn't matter (kinda
like your check numbers) then you could make this an autonumber field.

If you want Seq_No to start over for each year then on the form you use for
entering your data, you can set the control source of the control that
displays this number to something like:

=NZ(DMAX("Seq_No", "yourTable", "YearField = " Year(Date())), 0) + 1

This assumes that you have a text control (txt_Year) that contains a value
for the current year. To prevent someone from altering this Seq_No, you
should probably lock or disable the txt_Seq_No control on the form.

HTH
Dale

 

[slblakley]

It is bad database design to put more than one piece of information in a
single field. In your case you want to put a year and a sequence number in
the same field, don't!

If the DCR is a constant, leave it out. If you want to display this on a
form, it is easy to do.

Create a separate field for the year, and another for your sequence number
(I'll call this Seq_No). If Seq_No needs to start over at 001 each year,
then it needs to be an integer or long integer. If it doesn't matter (kinda
like your check numbers) then you could make this anautonumberfield.

If you want Seq_No to start over for each year then on the form you use for
entering your data, you can set the control source of the control that
displays this number to something like:

=NZ(DMAX("Seq_No", "yourTable", "YearField = " Year(Date())), 0) + 1

This assumes that you have a text control (txt_Year) that contains a value
for the current year. To prevent someone from altering this Seq_No, you
should probably lock or disable the txt_Seq_No control on the form.

HTH
Dale
--
Email address is not valid.
Please reply to newsgroup only.







- Show quoted text -

Dale,

Thanks so much for the response!!

I do have a few questions though...

the sequential number does not start over each year so that's the easy
part I guess...I need the number to show up on reports as "DCR-07-000"
for example can this be done if I don't make it part of the field? If
so, how?
I realized for the Form I can just name the field DCR and then set the
field to show the yr-sequential Number (07-000)...but will that hamper
my report...the DCR-07-000 is an important pre-set data value we use
and needs to be a part of the report...(at least for now).

 

[John W. Vinson]

the sequential number does not start over each year so that's the easy
part I guess...I need the number to show up on reports as "DCR-07-000"
for example can this be done if I don't make it part of the field? If
so, how?

If you're assuming that the only things you can display on a report are the
literal exact values of fields in the table - you're mistaken.

As suggested, store the year and the number, only. Set the Control Source of a
textbox on your report to

="DCR-" & YearField & "-" & Format(SeqField, "000")

John W. Vinson [MVP]