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

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

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
Asked:
donnagauthier
  • 3
  • 2
  • 2
  • +4
1 Solution
 
Arthur_WoodCommented:
You could write your own function to mmake the conversion.

AW
0
 
donnagauthierAuthor 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
 
jet46Commented:
Chr(65)  will convert numbers to letters.  65  in this case = A

Asc("A") will convert A to 65
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
mbizupCommented:
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
 
donnagauthierAuthor 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
 
mbizupCommented:
They are built in (at least in Access VBA, I beleive Visual Basic has them as well).
0
 
GRayLCommented:
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
 
Mike TomlinsonMiddle 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
 
donnagauthierAuthor 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
 
Mike TomlinsonMiddle 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
 
GrahamSkanCommented:
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

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

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