AutoNumber

  • Thread starter Thread starter ladybug via AccessMonster.com
  • Start date Start date
L

ladybug via AccessMonster.com

I have an AutoNumber that I would like to be four digits and then two digits
that we have to represent the year.
Ex. 0001-06
0002-06

I need it to work where it will continue as an AutoNumber for the first four
digits, but keep 06 all year.
Any suggestions?
 
Sorry, you can't do that. You also cannot expect that autonumbering will
stay sequential with no gaps. If a user starts to create a new record, but
doesn't complete it, the next number has been assigned already, so that
number will not be reused. Deletions will also leave gaps.
Autonumber fields should not really be used for meaningful data. Their
primary function is to be used for Primary Keys and for relating tables with
Foreign Keys. Even then, it is sometimes better to use meaningful data as
the primary key.
One other consideration is sorthing. You should have the year first, then
the number:
06-0001
06-0002
If you don't, then for multiple years, it will sort like this:
0001-04
0001-05
0001-06
0002-05
0002-06
Which, I don't think is what you would like.
My recommendation is that you use two long data type fields, one for the
year and one for the number and this routine to assign the next available
number:

Function GetNextNumber(lngYear as Long) As Long

GetNextNumber = Nz(DMax("[TheNumber]", "MyTable", "[TheYear] = " & _
lngYear), 0) + 1

End Function

This will also give you more flexibility in the way you sort or present the
number.
 
Back
Top