Autonumber?

  • Thread starter Thread starter ken
  • Start date Start date
K

ken

I am trying to set up a autonumber based on year, month,and autonumber
such as 401001. this is 4 from 2004,01 is January ,and 001 is start of numbering
i would like this to update in Febuary to 402001,March to 403001 and so on
Is this possible?
 
No, it is not possible. The values in an AutoNumber field are
system-generated and are used to provide a means of providing records in a
table with a unique identifier.

If some sort of date is meaningful for each record in your table, I would
suggest using a Date field. There are numerous built-in functions in Access
for parsing, displaying and doing 'math' with dates.
 
You can setup a routine which will "build" this number for you, although I
don't believe I'd store it in it's built form. I'm assuming that somewhere
you're storing the date your item is opened/built etc etc ... from here you
could get the month and year:

Dim strMonth As String
Dim strYear as String

strMonth = DatePart("m", YourDate) would give you the month
strYear = DatePart("yyyy",YourDate) would give you the year

As far as your sequence number, include a new column in your table named
lngSequence. To get the final part of your number, do this:

Dim lngMax As Long
lngMax = DMax("lngSequence","YourTable")

This would give you the largest value in that field. Add 1 to that number
and you've got your next sequence number (and this number would be stored in
you lngSequence field). So your displayed number would be:

strYear & strMonth & "-" & CStr(lngMax)
 
Back
Top