Auto Increment letters

[Corcoranl]

In access I want to Auto Increment letters instead of numbers i.e ( a,b,c,d….aa,ab,ac,ad……ba,bb,bc,bd…etc….

I am starting with a 'commodity code' something like GEGADZZADA and I want it to Auto Increment to GEGADZZADB and then GEGADZZADC and so on.

Any Ideas?

 

[georgedwilkinson]

This is an awful idea.

You would do this in VBA using the ASCII values for each of the symbols in your "count". There are several ways to break it up and it really depends on your knowledge of ASCII (or potentially EBCDIC), Visual Basic, and potentially, binary. You could also use a number and use one of the many

You would need to call your VBA function for every insert into the table (unless you just use a number and represent it as a string of characters). Again, not a good idea and prone to so many potential flaws and data anomalies I can't even begin to articulate them.

Curious, what comes after GEGADZZADZ? What comes before AAAAAAAAAA?

 

[Rabbie]

Is there a valid reason why you want to use characters rather than numbers. This will make your indexes much larger and slower.

As George has correctly pointed out you will have a horrendous overhead everytime you need to create a new value.

 

[gemma-the-husky]

but if you STILL want to do this, it would be better, IMO to still use an autonumber key to link your tables, but if you want to have this other alphabetic code, just store this in the main table

auto incrementing wont be too difficult. its fiddly rather than hard

just this sort of logic

if rightchar="Z" then
  rightchar="A"
  rightless1char = next(rightless1char) {see below - but what if this is a Z also!}
else
  rightchar = next(rightchar)
end if

HOWEVER
a) this gets trickier, if successive chars are Z's so it might be easier with a recursive solution
b) nextchar function - character incrementing is awkward anyway - you need to examine the ascii code of a char, increment it, and then turn it back into a char
c)assigning characters within a string is also tricky as you cant just reassign a character within a string - you have to use left,mid,right, and string concatenation functions


however, once you have it set up, it will work ok, you only have ot do it once per record, and its only a few clock cycles, so it wont be so slow.

 

[Atomic Shrimp]

but if you STILL want to do this, it would be better, IMO to still use an autonumber key to link your tables, but if you want to have this other alphabetic code, just store this in the main table

Whatever else you may do, definitely heed this advice. Do not use a synthetic, meaningful value for your primary key/identity - it will at some point go horribly wrong.

Use an autonumber for the identity (and probably keep it hidden from the user). Store your textual product codes in a text field.

If it were me, and I had no choice but to assign alpha codes, I would not bother trying to autoincrement them - I'd just generate a string of random alpha characters, check it didn't already exist and use that (if it does already exist, generate another one and try again).