Autonumber - prefix/sufix

[schumarg]

Hi,

Is it possible to have an Autonumber that I can assign a prefix to. Trying to add a prefix to the autonumber for the Order Number, "03" to desinate 2003.

Thanks,
Bob

 

[dcx693]

No, this is not possible, as far as I know. The autonumber field in access is a special type of long integer. Why do you want to do this?

Since the autonumber would apply to all records in a table, why not name the table with "03" in it to designate 2003?

 

[schumarg]

Hi,

Thanks for your reply.

This will be an ongoing system with one year flowing into another and would like a continious numbering system.

Just had a thought, not sure how it would be done. But would it be possible to generate the Autonumber, then convert that number to a string and supplement it with a Prefix string, then put that new string back to the table in another field. And also have this new number print on the order.

Sounds like a round-about way of doing it, if it is possible would really appreciate some assistance with the coding.

Thanks,
Bob

 

[neileg]

Why not leave the autonumber alone!

If you want what you describe, add another field and concatenate the autonumber with your prefix to produce 03000231 or whatever you want. Then display this new field but still retain the autonumber to do your linking behind the scenes.

You can use formatting to make the autonumber appear to be something like you want, but this won't accomodate a roll over into the next year, since the formatting won't change.

 

[dcx693]

Hmmm....I agree with neileg that your solution sounds too complicated. It might work, but sounds error-prone.

What if you just created another field in the database and set the default value equal to 03. Then when you need to enter records for 2004, change the default value to 04?

I wouldn't concatenate the autonumber field and the year number. You're basically duplicating information that's already in the autonumber field.

If you need to have a primary key based on the auto number and the year, just use a compound primary key.

 

[neileg]

Yeah, dcx693, better solution. However you might want to concat the autonumber and the prefix for display.

 

[schumarg]

Thanks Guys, you gave me some good ideas, now to make a choice. Thanks!!

Bob

luv this site!!!

 

[Pat Hartman]

Add a field that holds the date a record was added to the table. When you want to display a formatted autonumber field, use an expression.

Format(DateCreated,"yy") & "-" & Format(YourAutonumber,"000000")

This will produce something like -
03-000145

 

[schumarg]

THANKS!!!

Will try ASAP.