ID with Alpha

  • Thread starter Thread starter Mike
  • Start date Start date
M

Mike

The original thread seems to have diappeared from the newsgroup so I'm starting again.

We have a box id number say 1234 then we add a alpha for each element

in the box 1234A, 1234B, 1234C and so on. How can we have the alpha

part be added automaticaly.

I received this answer from Graham Mandeno which works great, but what happens when you get to the letter Z, we usally start going with AA, AB, AC etc.

Chr(Asc(Nz(DMax("ElementLetter","tblElements", _

"BoxNum=" & Me.BoxNum), "@")) +1 )

To break this down, working from the inner brackets outwards:

DMax(...) finds the highest letter used so far for the given BoxNum

Nz(..., "@") says to use an "@" sign (the ASCII character before "A") if there is no record for that BoxNum

Asc(...) + 1 takes the ASCII code value for the letter we have found and adds 1

Chr(...) converts the result back to a letter - the next one in the sequence.
 
Make the following changes:
1. DMax needs to check Right([Element],1)
2. If the letter in 1 is not Z increase the letter by 1 as you do in your
current procedure
3. If the letter in 1 is Z, check Len([Element])
4. If Len in 3 = 5 set [Element] = Left([Element],4) & "AA"
5. If Len in 3 is greater than 5, Check Mid([Element],5,1) and increase it by
1
6. Set [Element] = Left([Element],4) & "Letter From 5" & "A"


--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
(e-mail address removed)
www.pcdatasheet.com


Mid([Element],1)
The original thread seems to have diappeared from the newsgroup so I'm starting
again.

We have a box id number say 1234 then we add a alpha for each element

in the box 1234A, 1234B, 1234C and so on. How can we have the alpha

part be added automaticaly.

I received this answer from Graham Mandeno which works great, but what happens
when you get to the letter Z, we usally start going with AA, AB, AC etc.

Chr(Asc(Nz(DMax("ElementLetter","tblElements", _

"BoxNum=" & Me.BoxNum), "@")) +1 )

To break this down, working from the inner brackets outwards:

DMax(...) finds the highest letter used so far for the given BoxNum

Nz(..., "@") says to use an "@" sign (the ASCII character before "A") if there
is no record for that BoxNum

Asc(...) + 1 takes the ASCII code value for the letter we have found and adds 1

Chr(...) converts the result back to a letter - the next one in the sequence.
 
Please no HTML posts!

HTML posts expose the reader to various "HTML only" exploits.

Newsgroups posts should be in plain text, unless there are good reasons otherwise.

HTH,
TC

(snip)
 
Back
Top