ASCII Character

[accessvbahelp]

After I go through the entire alphabet for revisions we need to go to AA, AB, etc. I am having a heard writing code to go from Z to AA using the ASCII characters. Does anyone have any ideas how I can do this.

Thank you!!!

 

[SkipVought]

hi,

Please post the code that you have so far.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[accessvbahelp]

NewRev = Left(Rev, 1) 'Defines the revision code for the drawing under revision
Me.AllowEdits = True  'Allows the data to be edited
NewRevAlpha = Asc(NewRev) 'Converts the alpha character to a number
NewRevAlpha = NewRevAlpha + 1 'Indexes the alpha character ASCII code by one
If NewRevAlpha > 48 And NewRevAlpha < 57 Then
    NewRevAlpha = 65 - 49 + NewRevAlpha
End If 'If revision is a number it will add the number to the ASCII code for the correct letter
[COLOR=red]This is where I am stuck
If NewRevAlpha > 90 Then
    NewRevAlpha = 65
End If 'If revison letter goes beyond Z the next revision letter is AA, etc... [/color]
Debug.Print "NewRevAlpha is " & NewRevAlpha
Me!Revision.Value = Chr(NewRevAlpha) 'Places updated revision character in table
Me.AllowEdits = False 'Disables table edits
Me.Refresh 'Updates data in form and table

 

[PWise]

While this can be done my fingers are not so nimbel at the end of the day to spit out a function like this

BUT! If I will have a project like this i would keep the revisions a numeric and if i need it for display i would create a table to track revision letter

Revision # numeric
letters text

Revision letters
1 A
2 B
...
25 Y
26 Z
27 AA
28 AB
...

 

[TheAceMan1]

How are ya accessvbahelp . . .

For what you ask ... your code won't work. Your detection process includes a single character. [blue]How to detect[/blue] when you flip over to AA,AB,AC, ect or AAA, AAB, AAC, ect ... is the problem.

Provide some typical values for [blue]NewRev[/blue] crossovers!

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TheAceMan1]

accessvbahelp . . .

Came up with the following function. Rev is passed as a string (uppercase, lowercase or mixed), and the function returns the next REV increment in full uppercase. Put the code in a standard module so you can call it from anywhere:

[blue]Public Function NextRev(ByVal curRev As String) As String
   Dim newASC As Integer, idx As Integer, curAsc As Integer
   
   If curRev <> "" Then
      
      [green]'Insure curRev contains all letters & convert to uppercase[/green]
      For idx = 1 To Len(curRev)
         curAsc = Asc(Mid(curRev, idx, 1))
         
         If (curAsc > 64 And curAsc < 91) Or _
            (curAsc > 96 And curAsc < 123) Then
            Mid(curRev, idx, 1) = Chr(curAsc And 223)
         Else
            MsgBox "'Rev' Is Not All Letters!"
            Exit Function
         End If
      Next
      
      [green]'Test last character in curRev[/green]
      newASC = Asc(Right(curRev, 1)) + 1
      
      If newASC > 90 Then
         [green]'CrossOver occurs here[/green]
         NextRev = curRev & "A"
      Else
         [green]'Next increment occurs here[/green]
         NextRev = Left(curRev, Len(curRev) - 1) & Chr(newASC)
      End If
   Else
      MsgBox "No Rev Arguement!"
   End If
   
End Function[/blue]

Calls would look like:

[blue]Rev = "l"
[i]variable[/i] = NextRev(Rev) [green]'returns "M"[/green]

Me.TextboxName = NextRev("aZ") [green]'returns "AZA"[/green][/blue]

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[lionelhill]

You've got two issues (I'm assuming your revision-code is a prefix rather than a suffix because that's the way you seem to have written it):

(1) You need a way to see how many characters are involved in the revision code. For example, is AAdrawing the 1st version of file "Adrawing" or the 27th of "drawing"? You need to use a place-marker of some sort (AA_drawing)

(2) If you do this, your revision code is effectively a number base 26, and I suggest you treat it as such. Convert it to an integer or long, add one, and convert it back. In pseudocode:

("Number" should be an integer data-type)
Number = 0
While not place-marker
Number = (Number * 26) + (Asc(LeftCharacter) - Asc("A")
Remove first character
End While Loop
Number = Number + 1
While Number > 0
Text = Chr((Number mod 26) + Asc("A")) & Text
Number = Number / 26
End While Loop

You can, of course, implement this so your revision codes are written forwards or backwards, as suffixes or prefixes, however you want.

 

[Gammachaser]

This will work for the first 702 (27*26) revisions:

Function AlphaRev(ByVal RevNo As Integer) As String
Select Case RevNo > 26
    Case True
        AlphaRev = Chr(64 + Int((RevNo - 1) / 26))
        While RevNo > 26
            RevNo = RevNo - 26
        Wend
End Select
AlphaRev = AlphaRev & Chr(64 + RevNo)
End Function

For more revisions you would have to modify the code with an additional 'layer' starting with 'Select Case RevNo > 702' and put in the third Revision letter (i.e. 703 would produce 'AAA'.) If you need that many and you can't work it out, let me know.

 

[SkipVought]

this works until the LONG overflows, way past "ZZZZ"

Function NextInSeq(Val As String) As String
    Dim i As Integer, nVal, sByt As String, iVal As Integer
    Const BASE = 27
    Const MIN_ = 65
    
'convert to base 10
    
    For i = 0 To Len(Val) - 1
        sByt = Mid(Val, Len(Val) - i, 1)
        nVal = nVal + (Asc(sByt) - MIN_ + 1) * BASE ^ i
    Next
    
    nVal = nVal + 1   'incriment
    
'convert to BASE
    
    Do
        iVal = nVal Mod BASE
        If iVal = 0 Then iVal = 1
        nVal = nVal \ BASE
        NextInSeq = Chr(iVal + MIN_ - 1) & NextInSeq
        
    Loop While nVal > 0
End Function

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PaulF]

Skip, Your function works if fed upper case Characters only. If you change

sByt = Mid(Val, Len(Val) - i, 1)

to
sByt = Mid(Ucase(Val), Len(Val) - i, 1)

you can pass lower case revision numbers and get the correct answer

without UCase passing "aa" returns "AGG"
with UCase passing "aa" returns "AB"

PaulF

 

[SkipVought]

Does it make you happy?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[accessvbahelp]

Thank you everyone for your input!

I am very new at writing VBA code and I can't figure out why this is not working.
I decided not to use the ASCII code so I created a seperate table in Access called TblRev that references the number to the correct revision letter (example 1=A, 2=B,... 27=AA, 28=AB, etc)the column with the numbers is called Number and the column with the letters is called Revision.
I am trying to do a DLookup to reference the table but I can't get it to work. This is what I have so far. Like I said I am very new at writing VBA code... please be patient with me.

Private Sub btnApprove_Click()

Dim strNewRev As String
Dim NewRevAlpha As Integer

NewRev = Revision 'Defines the revision code for the drawing under revision
Me.AllowEdits = True 'Allows the data to be edited
NewRev = DLookup("Number", "tblRev", "Revision")
NewRev = NewRev + 1 'Indexes the alpha by one
Me!Revision.Value = NewRev 'Places updated revision character in table
Me.AllowEdits = False 'Disables table edits
Me.Refresh 'Updates data in form and table
End Function

 

[accessvbahelp]

I am sorry but I forgot to tell you what I get... Everytime I click on the Approve button it changes my revision to 2. I need the revision to be a letter and I need it to index by 1.

Thank you again everyone!

 

[accessvbahelp]

I just posted this on another thread and I thought it needs to be in both places.
You are correct! I am asking for help and I don't use it. Truth is I am very new to VBA. I got thrown into a project at work with no training or experience and I am trying to get through it. I really do appreciate all the help I am receiving on here! I will try the advice from AceMan again. I remember I tried it and I received an error code, I can't remember what the error code was because I have tried so many things. You are correct the DLookup is obviously not working so I need to move on. Thank you for pushing me. I need a kick in the butt!!!