Auto number with formatting

  • Thread starter Thread starter Jmcd
  • Start date Start date
J

Jmcd

Our group has the need to format file numbers 09-xxxx . The four digits to
the right I want to auto number and at year end want the xx- to change to the
next year ie. 10-xxxx. What is the best solution for this?
 
Jmcd said:
Our group has the need to format file numbers 09-xxxx . The four digits to
the right I want to auto number and at year end want the xx- to change to the
next year ie. 10-xxxx. What is the best solution for this?
Click to expand...


You should have two fields in the table for this. One for
the date (and time?) and another for the sequence number.
Whenever you want to display the file number for users (or
whatever you do with it), use a text box with an expression
like:
=Format(datefield, "yy") & Format(seqnum, "-0000")

You can calculate the sequence number by using a line of
code in the data entry form's BeforeUpdate event:
Me.seqnum = Nz(DMax("seqnum", "the table", _
"Year(datefield) = Year(Date()"), 0) + 1
 
In Access, the term "autonumber" means a specific data type that Access
creates. You have no control over this.

Instead, you can add a new field to your table that holds a "sequence
number", and use a routine to generate (the next) sequential number until
the year changes, then start over with sequence# = 0001.

Then, whenever you need to see "yy-####", use a query to concatenate the
last two digits of the year of the record (you are storing the date/time of
the record, right?) and the sequence number for that record.

No need to store this, as you can use your query to generate it on the fly.

Search on-line and at mvps.org/access for "custom autonumber" to find
routines for creating sequential values.

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
Back
Top