Re: ID with Alpha

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: PC Data*** (spam_at_nospam.spam)
Date: 04/27/04 

Date: Tue, 27 Apr 2004 20:33:35 GMT

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 Data***
Your Resource For Help With Access, Excel And Word Applications
                              resource@pcdata***.com
                                 www.pcdata***.com
Mid([Element],1)
"Mike" <mike_mandell@hotmail.com> wrote in message
news:#nvA#DJLEHA.1484@tk2msftngp13.phx.gbl...
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.
Quantcast