Solved

changing the char to a number

Posted on 2006-05-06
324 Views
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
Question by:donnagauthier

LVL 44

Expert Comment

You could write your own function to mmake the conversion.

AW
0

Author Comment

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

LVL 5

Expert Comment

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

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

LVL 61

Accepted Solution

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 Comment

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

LVL 61

Expert Comment

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

LVL 44

Expert Comment

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

LVL 85

Expert Comment

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 Comment

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

LVL 85

Expert Comment

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

LVL 76

Expert Comment

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

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…