Solved

VBA to Increment Revision Letters (Access 2000)

Posted on 2005-03-10
Medium Priority
684 Views
I'm looking for some bullet proof code to increment document (Generic Traveler) revision letters.  The database generates the letters and stores them as a string in a text field.  Right now I've got the capability of a one letter revisions.  For example, document 123 goes from rev A to rev B then rev C up to rev Z.  My code can't handle going beyond Z and of course now (after the DB has been used for 8 years) it needs to.  My simple code is:
If rstTraveler.EOF Then
Forms![Generic Traveler Form]!txtTravelerRev = "A"
Else
rstTraveler.MoveLast
'Increment Rev Letter
intRev = Asc(rstTraveler![Traveler Rev]) + 1
strRev = Chr(intRev)
Forms![Generic Traveler Form]!txtTravelerRev = strRev
End If

I need to be able to generate rev letters beyond Z and I'm think maybe Z, AA, BB...ZZ, AAA, BBB, ZZZ, etc.
Anyone have anything that does something like this.
0
Question by:schmir1
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2
• 2

LVL 41

Accepted Solution

shanesuebsahakarn earned 1500 total points
ID: 13512143
Hmm, let's see:

Dim Carry As Boolean, I As Long

If rstTraveler.EOF Then
Forms![Generic Traveler Form]!txtTravelerRev = "A"
Else
rstTraveler.MoveLast
'Increment Rev Letter
strRev = rst![Traveler Rev]
Carry=True
For I=Len(strRev) To 1 Step -1
If Carry=True Then
If Mid\$(strRev,I,1)="Z" Then
Mid\$(strRev,I,1)="A"
Else
Mid\$(strRev,I,1)=Chr\$(Asc(Mid\$(strRev,I,1))+1)
Carry=False
End If
End If
Next
If Carry=True Then strRev="A" & strRev
End If
Forms![Generic Traveler Form]!txtTravelerRev = strRev

I *think* that will do it.
0

Author Comment

ID: 13521860
It works great except it generated the letter a little different then my user would like.  It generates letters like:
A...Z, AA, AB, AC...AZ, BA, BB, BC, etc.

My user would like:
A...Z, AA, BB, CC...ZZ, AAA, BBB, CCC, etc.

If it's a big deal I might be able to talk them into the first way.
0

LVL 41

Expert Comment

ID: 13521900
That's a bit tougher...if you can write down the logic of how you want the letters to be generated, I can try and modify the code for you.
0

Author Comment

ID: 13539995
I took a shot at modifying it.  Not the most concise code in the world but it works.  Thanks for your help

Public Function GenRevLetter() As String
Dim rst As Recordset
Dim strQuery As String
Dim strRev As String
Dim intLen As Integer
Dim intRev As Integer

strQuery = "SELECT [Traveler Rev], [Generic Traveler ID], [Traveler #] FROM [Generic Traveler]" & _
" WHERE [Traveler #] =" & "'" & Forms![Copy to New Generic Traveler]!txtTravelerNum & "'" & _
" ORDER BY [Generic Traveler ID]"
Set rst = CurrentDb.OpenRecordset(strQuery, dbOpenSnapshot)
If rst.EOF Then
Forms![Generic Traveler Form]!txtTravelerRev = "A"
Else
rst.MoveLast
rst.MovePrevious
'Increment Rev Letter
strRev = rst![Traveler Rev]
intLen = Len(strRev)

If IsNumeric(strRev) Then
intRev = strRev
strRev = intRev + 1
Else
Select Case intLen  'Generate rev letters to match current number of letters
Case 1  '1 letter rev
If strRev = "Z" Then
strRev = "AA"
Else
strRev = Chr(Asc(strRev) + 1)
End If
Case 2 '2 to 3 letter rev
If strRev = "ZZ" Then
strRev = "AAA"
Else
strRev = Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1)
End If
Case 3 '3 to 4 letter rev
If strRev = "ZZZ" Then
strRev = "AAAA"
Else
strRev = Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1)
End If
Case 4 '4 to 5 letter rev
If strRev = "ZZZZ" Then
strRev = "AAAAA"
Else
strRev = Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1)
End If
Case 5 '5 letter rev to numberic rev
If strRev = "ZZZZZ" Then
strRev = "1"  'starts the number sequence
Else
strRev = Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1) & Chr(Asc(strRev) + 1)
End If
Case Else    ' Other values.
LogEvt "GenRevLetter can't generate a rev letter for this document", vbCritical, "GenRevLetter Error (CGT-5)"
End Select
End If
End If
GenRevLetter = strRev
End Function

0

Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses
Course of the Month9 days, 1 hour left to enroll