Is there a way to add to a letter ie. A + 1 = B 2

[mflancour]

I have a database that keeps track of revisions by letter, and I want to have it automacally update this rev letter through code. Is there a way to do this?

 

[filf]

Yeah you could populate an array with all letters in the alphabet.

You could then determine the pos of the cur letter in the array with code something like the following:

Option Explicit
Dim testArr As Variant

Public Sub addtoLetter(letter, inc)
Dim i
For i = 1 To UBound(testArr)
If testArr(i) = letter Then MsgBox testArr(i + inc)
Next
End Sub

Private Sub Command1_Click()
Call addtoLetter("d", 4)
End Sub

Private Sub Form_Load()
testArr = Array("a", "b", "c", "d", "e", "f", "g", "h", "i", "etc&quot
End Sub

obviously you would want to dim the array more fully etc but create a test form with a button called command1 on it paste this code - away you go.

 

[JoanneM]

Hi there

if it's just the one letter may be the snippet below will help

Function nextchar(char As String) As String
nextchar = Chr$(Asc(char) + 1)
MsgBox nextchar
End Function


you can omit the msg box


to check it - just pop

? nextchar("c&quot

in the immediate window (View submenu) and press enter
the msgbox will display d


hope this helps

regard

jo

 

[mflancour]

Thanks! I should have thought of this. It should work great since I'll need to skip some letters like o and i.

 

[mflancour]

Thanks, both of you!

 

[DabblingDan]

I something similar with an item number. The first time item is assigned it does not receive a letter (I refer to it as suffix). Every re-issue gets an incremental letter. The function I use checks to see what the highest suffix assigned is and then increments it by one.
I don't know if this will help, I am not a coder by trade, but this has worked for me. Use what you can, if you can.

This function passing the number key of the item it needs to increment.

Public Function GetSuffix(lkup As Long) As String
On Error GoTo GetSuffixERR
Dim ret As String
Dim currentHigh As Integer

Dim db As Database
Dim rs As Recordset
Dim sql As String
Dim qdf As QueryDef

sql = "SELECT Max (f_OurSuffix) AS High_OurSuffix FROM tblAdjustments " & _
"WHERE f_OurRef = " & lkup
Set db = CurrentDb
Set qdf = db.CreateQueryDef("", sql)
Set rs = qdf.OpenRecordset(dbOpenSnapshot)
currentHigh = Asc(rs!High_OurSuffix)
ret = Chr((currentHigh + 1))
GetSuffix = ret

GetSuffix_Exit:
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
Exit Function

GetSuffixERR:
'if there is no maximum suffix (this is first resend)
'"currentHigh = Asc(rs!High_OurSuffix)" will cause error because High_OurSuffix
'is null. This set the initial value to 64 (1 less than A)

If Err.Number = 94 Then 'INVALID USE OF NULL
currentHigh = 64
Resume Next
Else
MsgBox "GetSuffix Error: " & str(Err.Number) & " " & Err.Description
Resume GetSuffix_Exit
End If

End Function