1. Registered User
Join Date
Nov 2004
Posts
10

Hi i am trying to use an alphanumeric autonumber E.g.

AAA0001

So i can go through from AAA0001 - AAA9999
And then AAB0001-AAB9999 etc

You get the idea....

How is this doen in access - any ideas please

Please also note that teh database its being used for will eventually migrate up to MS SQL but not for a while...

Thanks !!!

2. Cavalier King Charles
Join Date
Dec 2002
Location
Préverenges, Switzerland
Posts
3,740
it isn't done in access!

a's autonumbers are long integers: you get 2.4 billion of them but they don't contain alphas.

you can find fanatics on the web who insist that primary keys should never have a "meaning" (i.e. they should not be your invoice number). a's autonumbers fit the bill as small meaningless bits of glue you can use to stick tables together.

izy

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
you can do it, or get close to it, but you would have to write a function to handle it. Unless you have a 64 bit processor you wouldn't be able to get AAAA0000 as the range that requires is outside the scope of the long datatype on 32 bit machines.

You would have to do a comparision and move left to right factoring out the ranges for the letters
eg 'note this is not "real code" just the methodology
if orderno> (26*26*26*10*10*10*10) then
newno=chr\$(65+(orderno-(26*26*26*10*10*10*10)/26))
orderno=orderno-(26*26*26*10*10*10*10)/26))
else
newno="A"
endif
if orderno> (26*26*10*10*10*10) then
newno=newno & chr\$(65+(orderno-(26*26*10*10*10*10)/26))
orderno=orderno-(26*26*10*10*10*10)/26))
else
newno=newno & "A"
endif
if orderno> (26*10*10*10*10) then
newno=newno & chr\$(65+(orderno-(26*10*10*10*10)/26))
orderno=orderno-(26*10*10*10*10)/26))
else
newno=newno & "A"
endif
newno=newno& format(orderno,"0000")

note this is indicative, I haven't trested it, written it on the fly here, so the details may be wrong but the basic logic flow shouldbe ok. You may eneed to adjust factors etc.....

4. Registered User
Join Date
Mar 2004
Location
belgium
Posts
290
always ask yourself is the autonumbering necessary, besides its easyness.

in this case you could solve this by using a textfield and some arithmatic and stringmanupulation,

befor each append, find the lastrecord (movelast)
split the string in first part ("AAA") and second part val("999")

increase second part with 1

check max value (999)

if greater change first part to "AAB" and second part to "0001"

combine first and second part

append record
and assign the newly created string to the proper field.

it´s just an idea.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•