What is best way to create an autonumber that has a prefix?

[peterkennett]

Autonumber works great, but my boss needs the ID to be a number that begins with the 2 digit year. For example 08-465 and 08-466.

I know this must be a common requirement, so what is the best way to handle it? Should I simply create a new text field ([newID] and then use VB in a newrecord event that:

Me.newID = DatePart("YY", Now) & "-" & Me.ID

This works, but I want to see if anyone thinks there is a better way?

Peter

[MindBender77]

Autonumber works great, but my boss needs the ID to be a number that begins with the 2 digit year. For example 08-465 and 08-466.

This works, but I want to see if anyone thinks there is a better way?

Peter

Format your AutoNumber field in your table as such:
Format: "08-"000

Bender

[peterkennett]

I guess that would work, as long as I code in some way to change that default setting every Jan 1, to match the new year.

Thanks!

Peter

[Stewart Ross]

I would store the year in the record itself, as a numeric field whose default value is '=year(date())' . You can combine this with the value of the autonumber field to create the record ID which you need (in format YY-NNNN).

If you just format the autonumber value without explicitly storing the year you will not be able to answer a query such as 'list all records for year 08 (or 09 etc) which are over X in value', say.

I use similar techniques when dealing with purchase orders for differing financial years (the year number is part of the record).

Although an autonumber is unique, the customer requirement to have the year as well indicates a compound key (year+reference ).

Does your customer require that the ID's restart each year (e.g. from 1 Jan 09 reference 09-0001? If so, the autonumber solution has a difficulty, as autonumbers are not directly resettable to a start value. There are other ways to cope with this using a Long field and incrementing the value programmaticall y.

Hope this helps.

Regards

Stewart

[peterkennett]

Stewart, that's a great idea, and I already had a date field to each record, so I already had the information needed to create the YYYY-0000 format for my ID numbers.

But, yes, I do have to reset the numbers each year back to 1. I had completely forgot about this issue! So what is the best way to use an incrementing Long Field as you stated.

Do I..

1: create a new table with an ID field - Table!NewID.ID
2. Begin with a "1" in the ID field each Jan 1st.
3. Pull this table's field every time I add a new record and use it for the unique ID number for my records on my main form
4. Increment the number in Table!NewID.ID when I update my record.

Is that the standard way around this issue?

Peter