I need to increment a string of alpha/numeric characters

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need to increment a string of alpha/numeric characters from 00000 to ZZZZZ.
The string will always be 5 characters long. Any suggestions?
 
Phil said:
I need to increment a string of alpha/numeric characters from 00000
to ZZZZZ. The string will always be 5 characters long. Any
suggestions?
Click to expand...

What is the pattern for incrementation? Do you mean to go

00000
00001
...
00009
0000A
0000B
...
0000Z
00010
00011
...
00019
0001A
...

and so on? Essentially counting in base 36?
 
Yes, that is exactly correct.

Dirk Goldgar said:
What is the pattern for incrementation? Do you mean to go

00000
00001
...
00009
0000A
0000B
...
0000Z
00010
00011
...
00019
0001A
...

and so on? Essentially counting in base 36?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Yes, that is exactly correct.

Dirk Goldgar said:
What is the pattern for incrementation? Do you mean to go

00000
00001
...
00009
0000A
0000B
...
0000Z
00010
00011
...
00019
0001A
...

and so on? Essentially counting in base 36?

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Phil said:
Yes, that is exactly correct.
Click to expand...

Here's a quickie function I threw together. Testing is up to you.

'----- start of code -----
Function IncrementAlphaNumeric(pValue As String) As String

Dim strValue As String
Dim intDigit As Integer
Dim intCarry As Integer
Dim I As Integer

strValue = UCase(pValue)

If strValue Like "*[!0-9A-Z]*" Then
Err.Raise 5 ' Invalid argument
End If

intCarry = 1 ' initial increment

For I = Len(strValue) To 1 Step -1

If intCarry = 0 Then
Exit For
End If

intDigit = Asc(Mid(strValue, I, 1)) + intCarry

intCarry = 0

Select Case intDigit
Case 58
intDigit = 65
Case 91
intDigit = 48
intCarry = 1
End Select

Mid(strValue, I, 1) = Chr(intDigit)

Next I

IncrementAlphaNumeric = strValue

End Function

'----- end of code -----

Note that the function determines the number of output digits from the
number of input digits, so the input string must be zero-padded on the
left to the full anticipated length.
 
Yes, that is exactly correct.
Click to expand...

Then you just need a Base-36 conversion:



Public Function Convert(SomeNumber As Long) As String

Dim dwNumerator As Long
Dim wRemainder As Integer

' just for maximum flexibility
Const c_wBase As Integer = 36

' these are available digits
Const c_strDigits = _
"0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"

' chop of the least significant digit
dwNumerator = SomeNumber \ c_wBase
wRemainder = SomeNumber - c_wBase * dwNumerator

If dwNumerator = 0 Then
' finished
Convert = Mid$(c_strDigits, wRemainder + 1, 1)

Else
' convert the rest and then add this digit on the end
Convert = Convert(dwNumerator) & _
Mid$(c_strDigits, wRemainder + 1, 1)

End If

End Function


Hope that helps


Tim F
 
Dirk,

Thanks, it worked like a champ.

Phil B

Dirk Goldgar said:
Phil said:
Yes, that is exactly correct.
Click to expand...

Here's a quickie function I threw together. Testing is up to you.

'----- start of code -----
Function IncrementAlphaNumeric(pValue As String) As String

Dim strValue As String
Dim intDigit As Integer
Dim intCarry As Integer
Dim I As Integer

strValue = UCase(pValue)

If strValue Like "*[!0-9A-Z]*" Then
Err.Raise 5 ' Invalid argument
End If

intCarry = 1 ' initial increment

For I = Len(strValue) To 1 Step -1

If intCarry = 0 Then
Exit For
End If

intDigit = Asc(Mid(strValue, I, 1)) + intCarry

intCarry = 0

Select Case intDigit
Case 58
intDigit = 65
Case 91
intDigit = 48
intCarry = 1
End Select

Mid(strValue, I, 1) = Chr(intDigit)

Next I

IncrementAlphaNumeric = strValue

End Function

'----- end of code -----

Note that the function determines the number of output digits from the
number of input digits, so the input string must be zero-padded on the
left to the full anticipated length.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
I would load up an array with the individual ASCII characters for each
position in the string and then add 1 and then rebuild the string and
convert the values back.

AAAAA becomes translates to 65 65 65 65 65 65
which becomes 65 65 65 65 65 66
which converts back to A A A A A B

http://www.lookuptables.com/ may come in handy

David H
 
How do you do this in Private Sub?

I can't get it to work.
Click to expand...

What doesn't work? I think I remember testing this at the time. Use of
keywords like Private and Public are documented in the help files -- you
just have to understand about visibility and scope in order to do anything
in modular programming. And there is not much point in calling it a Sub
because it has to return a value...


B wishes


Tim F
 
Back
Top