Re: I need to increment a string of alpha/numeric characters

Dirk Goldgar

unread,

Mar 1, 2005, 11:18:14 AM3/1/05

to

"Phil" <Ph...@discussions.microsoft.com> wrote in message
news:E9CB5B0E-A3BE-4B6A...@microsoft.com
> I need to increment a string of alpha/numeric characters from 00000
> to ZZZZZ. The string will always be 5 characters long. Any
> suggestions?

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)

Phil

unread,

Mar 1, 2005, 11:35:01 AM3/1/05

to

Yes, that is exactly correct.

Phil

unread,

Mar 1, 2005, 11:35:03 AM3/1/05

to

Yes, that is exactly correct.

Dirk Goldgar

unread,

Mar 1, 2005, 12:50:13 PM3/1/05

to

"Phil" <Ph...@discussions.microsoft.com> wrote in message

news:D4FB3715-9DC0-47CC...@microsoft.com

> Yes, that is exactly correct.
>
> "Dirk Goldgar" wrote:
>
>> "Phil" <Ph...@discussions.microsoft.com> wrote in message
>> news:E9CB5B0E-A3BE-4B6A...@microsoft.com
>>> I need to increment a string of alpha/numeric characters from 00000
>>> to ZZZZZ. The string will always be 5 characters long. Any
>>> suggestions?
>>
>> 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?

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.

Tim Ferguson

unread,

Mar 1, 2005, 2:14:39 PM3/1/05

to

"=?Utf-8?B?UGhpbA==?=" <Ph...@discussions.microsoft.com> wrote in
news:D4FB3715-9DC0-47CC...@microsoft.com:

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

>> and so on? Essentially counting in base 36?

> Yes, that is exactly correct.
>

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

Phil

unread,

Mar 1, 2005, 2:59:03 PM3/1/05

to

Dirk,

Thanks, it worked like a champ.

Phil B

Phil

unread,

Mar 1, 2005, 3:01:26 PM3/1/05

to

Tim,

Thanks for the response but I did get Dirk's working and I'm all set now.

David C. Holley

unread,

Mar 1, 2005, 3:34:02 PM3/1/05

to

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

anon...@discussions.microsoft.com

unread,

Mar 1, 2005, 11:07:33 PM3/1/05

to

USE NESTED LOOPS WITH VB OR c++

>-----Original Message-----
>I need to increment a string of alpha/numeric characters
from 00000 to ZZZZZ.
> The string will always be 5 characters long. Any
suggestions?

>.
>

Mike

unread,

Jun 30, 2005, 5:12:01 PM6/30/05

to

How do you do this in Private Sub?

I can't get it to work.

Thanks

Tim Ferguson

unread,

Jul 1, 2005, 12:05:39 PM7/1/05

to

"=?Utf-8?B?TWlrZQ==?=" <Mi...@discussions.microsoft.com> wrote in
news:0ADE1519-57A1-4EE2...@microsoft.com:

>
> How do you do this in Private Sub?
>
> I can't get it to work.
>

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