Formatting Autonumber columns

  • Thread starter Thread starter Aztec
  • Start date Start date
A

Aztec

I need to have an autonumber column in a table but need to have the current
month incorporated into that number too. For example a record entered in
July could be 07056, 07057, etc. Up until now I have been formattiing the
autonumber as "07"000 which works fine for july but if I change the "07" to
"08" in august all the records will change to "08". I would also like to
reset the autonumber each month back to 0, so that at the start of each
month the first rcord will be, for eg, 08001, 09001, etc..

Having to manually change something each month isn't a problem but idealy it
would be automated.

Any ideas?
 
Hi

You cannot do as you require using a 'pure' Autonumber
field, you will need to write your own 'Next Unique
Number' function, and in your table define the
corresponding column as a long or a string, (I would use
string)

Regards

Ken Reay
 
This isn't too difficult to achieve but you will have to write it in VBA.
The date portion is just done by using the current date and the number could
be stored in a table and retrieved it when needed for your next number as
in lastNum + 1. Don't understand what you mean by if you change 07 to 08
all the numbers change. To reset the number to zero you would want to also
store the month of last record so when
If LastMonthNumber < CurrentMonthNumber Then
ResetNumber = 1
End If

Here is a MS KB help on how to do the number part in AC2000
http://support.microsoft.com/default.aspx?scid=kb;en-us;210194
 
Back
Top