Special ID number

  • Thread starter Thread starter woodrat
  • Start date Start date
W

woodrat

I need to make an expression that will return a special ID number for each
item. The ID number needs to start out with the current year only (like 2009)
a - then a 3 didgt number that automatically uses the next available number.
like 2009-057, 2009-058, 2009-059, 2009-060.
 
Woodrat,

When you say the current year, I assume you mean the year when the record is
created?

And then, do you want the suffix numbering to re-start at 1 at the beginning
of the subsequent year?

Will there never be more than 999 items?
 
Yes, start with the current year, and then on Jan, 1 of the next year use
that year.
So it will always start with the current year.

Yes, restart the numbering on Jan 1 of each year.

4 didgt to be safe.

Like 2009-0152, 2009-0153 ........, on Jan 1 start over 2010-0001, 2010-0002.

Thanks
 
Woodrat,

My recommendations:

1. Add an AutoNumber data type field to this table, to serve as the Primary
Key, and to relate the records in this table with other data.

2. I am not sure whether you already have a date field in the table. But
if not, add one, let's call it DateCreated, and set its Default Value
property to:
Date()

3. Do not store your combined identifying number in the table. Just have
another field, Number (Integer) data type, let's call it YearSerial. On the
form that you use for data entry, set the Default Value of a textbox
(probably hidden, i.e. Visible = No) bound to this field, to the equivalent
of this:
Nz(DMax("[YearSerial]","NameOfYourTable","Year([DateCreated])=Year(Date())"),0)+1

4. Whenever you need this combined ID for your purposes on form or report,
set it as a calculated field in the query that the form or report is based
on, using syntax like this:
CombnedID: Format([DateCreated],"yyyy") & "-" & Format([YearSerial],"0000")
Or, similarly, enter directly to the Control Source of an umbound textbox on
your form or report:
=Format([DateCreated],"yyyy") & "-" & Format([YearSerial],"0000")
 
Would it be possible to use a 4 place decimal number with AutoNumber in the
data field instad of an integer? Like 2010.0156 and set it to start counting
from 4th place to the right of the decimal. Like 2010.0156, 2010.0157,
2010.0158? That would do the same thing for me, the decimal could replace the
dash. Or set up an auto number from scratch?

Steve Schapel said:
Woodrat,

My recommendations:

1. Add an AutoNumber data type field to this table, to serve as the Primary
Key, and to relate the records in this table with other data.

2. I am not sure whether you already have a date field in the table. But
if not, add one, let's call it DateCreated, and set its Default Value
property to:
Date()

3. Do not store your combined identifying number in the table. Just have
another field, Number (Integer) data type, let's call it YearSerial. On the
form that you use for data entry, set the Default Value of a textbox
(probably hidden, i.e. Visible = No) bound to this field, to the equivalent
of this:
Nz(DMax("[YearSerial]","NameOfYourTable","Year([DateCreated])=Year(Date())"),0)+1

4. Whenever you need this combined ID for your purposes on form or report,
set it as a calculated field in the query that the form or report is based
on, using syntax like this:
CombnedID: Format([DateCreated],"yyyy") & "-" & Format([YearSerial],"0000")
Or, similarly, enter directly to the Control Source of an umbound textbox on
your form or report:
=Format([DateCreated],"yyyy") & "-" & Format([YearSerial],"0000")

--
Steve Schapel, Microsoft Access MVP


woodrat said:
Yes, start with the current year, and then on Jan, 1 of the next year use
that year.
So it will always start with the current year.

Yes, restart the numbering on Jan 1 of each year.

4 didgt to be safe.

Like 2009-0152, 2009-0153 ........, on Jan 1 start over 2010-0001,
2010-0002.

Thanks
Click to expand...
.
Click to expand...
 
Thanks, you have been a great help.

Steve Schapel said:
Woodrat,

No. An AutoNumber field is by definition a Long Integer.

--
Steve Schapel, Microsoft Access MVP





.
Click to expand...
 
Back
Top