[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 379

# changing the char to a number

In my MS Access & VB database application I have a field called rev, which is a revision to  specification of criteria according to which an inspection to product takes place. Every time a rev for the specs is accepted and approved, the VB code should assign a rev name (which is a character designation) to it. The revs go in this fassion: A, B, C, .... X, Y, Z, AA, AB, AC, ...... ZX,ZY, ZZ. My question is how can I accomplish that. I tried to look for a ToNumber Function to change the "A" to its number value which lets say is the number 20, then I add 1 to this "20" which becomes 21 then if such function exists I do ToChar(21). But a problem would arise when I reach the Z and I follow the same logic I will not be able to get from Z to AA but I  will get somether characted. Could some one help me please?

Thanks
0
donnagauthier
• 3
• 2
• 2
• +4
1 Solution

Commented:
You could write your own function to mmake the conversion.

AW
0

Author Commented:
Hey Arthur thanks for replying :)

but what is the core function that would do the work, I tried the help for ToString, To Number etc and I did not get anything. Or do you mean that should get a huge switch statement case A then B, Case B then C, Case Z then AA etc? But isn't that too long to type? I mean I have  26 alphabet a-> z then I mupltiply that by 26 because for each letter A as prefix I could have 26 options (A->Z) as suffix.
So in this case I would have 26X26 +26 = 702 cases to include in the switch statement. Isn't there a faster way? Or is there any other logic or tool I could use to simply the task?

Appreciating your patience and feed back guys

0

Commented:
Chr(65)  will convert numbers to letters.  65  in this case = A

Asc("A") will convert A to 65
0

Commented:
Try this (Both of these functions could use some error handling for values that are out of the ranges):

'This will convert a string (2 uppercase letters) to a number 1 to 702
Function GetNumber(str As String) As Integer
Select Case Len(str)
Case 1:
GetNumber = Asc(str) - 64
Case 2:
GetNumber = (Asc(Left(str, 1)) - 64) * 26 + (Asc(Right(str, 1)) - 64)
End Select
End Function

'This is a function that I've used to convert a number (1-702)  to an excel column (A to ZZ):
Function MakeExcelColumn(n As Integer)
Dim A, ColumnCount As Integer
Dim FirstLetter, SecondLetter As String
A = 65

ColumnCount = n - 1
FirstLetter = IIf(Int(ColumnCount / 26) = 0, "", Chr((Int(ColumnCount / 26) - 1) + A))
SecondLetter = Chr((ColumnCount - 26 * Int(ColumnCount / 26)) + A)
MakeExcelColumn= FirstLetter & SecondLetter

End Function
0

Author Commented:
thanks mbizup
I think your answer deserves to be selected, I just want to make sure I fully understand it
Now is the "Left", and "Right" functions user defined or are they built in.
thanks
0

Commented:
They are built in (at least in Access VBA, I beleive Visual Basic has them as well).
0

Commented:
This will take you up to 676 reference changes.  You need a number r indentifying, sequentially each change, and this will give you two digits ranging from A to ZZ.

Iif((r-1)\26 < 1,chr(64 + 1 + (r-1) mod 26),Iif((r-1)\26 < 27,chr(64 + (r-1)\26) & chr(64 + 1 + (r-1) mod 26),""))

It's easy to add the "third" digit.
0

Middle School Assistant TeacherCommented:
This is VB6 code...

Here is a generic algorithm for incrementing a "revision sequence".

Change the line below in the code to suit your needs:

chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

You can add digits (or other characters) or omit letters as you see fit.  You can even change the order of the characters etc...

For instance, if you wanted to include digits (but not zero) in the sequence, and the digits should come after the letters, then use:

chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ123456789"

Option Explicit

Private Sub Command1_Click()
Text1.Text = NextRevision(Text1.Text)
End Sub

Private Function NextRevision(Optional ByVal curRevision As String) As String
Dim i As Integer
Dim char As String
Dim chars As String
Dim index As Integer
Dim charArray() As String

' modify the line below to include the characters you
' want in the sequence
chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"

' blank revision passed in, return first letter in sequence
curRevision = Trim(UCase(curRevision))
If curRevision = "" Then
NextRevision = Left(chars, 1)
Exit Function
End If

' make sure current revision has only valid characters in it
' otherwise...return first letter in sequence
For i = 1 To Len(curRevision)
char = Mid(curRevision, i, 1)
If InStr(chars, char) = 0 Then
MsgBox "Returning: " & Left(chars, 1), vbCritical, "Invalid Starting Revision: " & curRevision
NextRevision = Left(chars, 1)
Exit Function
End If
Next i

' build a character array from the current revision
ReDim charArray(Len(curRevision) - 1)
For i = 1 To Len(curRevision)
charArray(i - 1) = Mid(curRevision, i, 1)
Next i

' see if the just the last letter needs to be "incremented"
char = charArray(UBound(charArray))
index = InStr(chars, char)
If index < Len(chars) Then
charArray(UBound(charArray)) = Mid(chars, index + 1, 1)
NextRevision = Join(charArray, "")
Exit Function
End If

' last letter "rolled over"
' propagate the "carry over" as far as it needs to go
charArray(UBound(charArray)) = Left(chars, 1)
For i = UBound(charArray) - 1 To 0 Step -1
char = charArray(i)
index = InStr(chars, char)
If index < Len(chars) Then
index = index + 1
charArray(i) = Mid(chars, index, 1)
NextRevision = Join(charArray, "")
Exit Function
Else
charArray(i) = Left(chars, 1)
End If
Next i

' "carry over" propagated all the way thru sequence
' add first letter of sequence to the beginning of the revision
NextRevision = Left(chars, 1) & Join(charArray, "")
End Function
0

Author Commented:
hey Idle Mind thanks for this excellent piece of code. I am new to VB and I was not able get anything from the help files ceoncerning the UBound function you mention in your code. Could yuo please help me understand it. thanks
0

Middle School Assistant TeacherCommented:
The UBound() function returns the upper bound (the highest index) for an Array.  In this case the Array is called "charArray".  I tested the code in a MSAccess database and it worked fine.   I just had to change the button click code to:

Private Sub Command1_Click()
Text1.SetFocus
Text1.Text = NextRevision(Text1.Text)
End Sub

0

Commented:
I know that the question has been answered, but here is some generic code that also does the job. It does have some data validation.

Function GetNextRev(strCurrentRev As String) As String
Dim p As Integer
Dim lRevisionNo As Long
Dim a As Integer
Dim m As Integer
Dim strChar As String
Dim iCharValue As Integer

Const SymbolCount = 26
Const FirstSymbol = "A"

a = Asc(FirstSymbol)
For p = 1 To Len(strCurrentRev)
iCharValue = Asc(Mid\$(strCurrentRev, p, 1)) - a + 1
If iCharValue < 1 Or iCharValue > SymbolCount Then
MsgBox "Illegal input"
Exit Function
Else
lRevisionNo = lRevisionNo + iCharValue * SymbolCount ^ (Len(strCurrentRev) - p)
End If
Next p
lRevisionNo = lRevisionNo + 1
Do While lRevisionNo > 0
m = lRevisionNo Mod SymbolCount
If m = 0 Then
strChar\$ = Chr\$(SymbolCount)
Else
strChar\$ = Chr\$(m + a - 1)
End If
GetNextRev = strChar\$ & GetNextRev
lRevisionNo = lRevisionNo - m
lRevisionNo = lRevisionNo / SymbolCount
Loop
End Function
0

## Featured Post

• 3
• 2
• 2
• +4
Tackle projects and never again get stuck behind a technical roadblock.