By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,905 Members | 2,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,905 IT Pros & Developers. It's quick & easy.

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

P: 12
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
Feb 11 '08 #1
Share this Question
Share on Google+
4 Replies


MindBender77
100+
P: 234
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
Feb 11 '08 #2

P: 12
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
Feb 12 '08 #3

Expert Mod 2.5K+
P: 2,545
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 programmatically.

Hope this helps.

Regards

Stewart
Feb 12 '08 #4

P: 12
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
Mar 13 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.