Auto number

[Amelia]

I have a field called [Invoice Number] and currently have it set to text with
an input mask.

I would like it to automatically give a new record an invoice number in
sequencital order. But it needs to be in this format G- current year (09)
infinite numbers starting with 200.

So for example my first entry would have an invoice number of G-09200, next
would be G-09201, etc... But then at the begining of a new year it needs to
revert back. So in 2010 the invoice number would be G-10200, G-10201, etc...
Is this possible?

 

[Jeff Boyce]

Possible, yes. A good idea, no.

Database design principals call for "one fact, one field". You're trying to
stuff three facts into a single field.

Instead, use the tools Access provides, i.e., a query. Store a date in a
date/time field and use Year([YourDate]) to get the year of that date. Use
a field to hold a [SequenceNumber].

Do NOT use Access Autonumber to generate that [SequenceNumber] -- Access
Autonumber generates a unique row identifier, but is generally unfit for
human consumption and is NOT guaranteed to be sequential.

In your query, concatenate the literal "G", the rightmost two characters of
the Year([YourDate]), and your [SequenceNumber].

To get the [SequenceNumber] value, use the DMax() function to find the
largest sequence number used so far in the Year([YourDate]), then add 1.

Regards

Jeff Boyce
Microsoft Office/Access MVP