ID with Alpha

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

Mike

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.
 
Hi Mike

DO NOT store the box number and the letter in the same field. The box
number should be the foreign key field that relates your tblElements table
to your tblBoxes table.

Now, assuming your two fields are names BoxNum and ElementLetter, the next
letter can be found from:

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.
 
Back
Top