Sequence records with "a", "b", "c" etc

[carmenv323]

Hello,


I have the following code which searches for the last sequence number in the table in order to create folders. If there is more than one project number I would like for those records to output with the sequence number and an "a" or "b" etc. The following works ok but starts the first one with a "b" rather than "a". What is the best way to alter this to do that? Thank you in advance.

 If IsNull(frm.SequenceNum) Or (frm.SequenceNum = 0) Then
        Dim intLastSeqNum As Integer
            intLastSeqNum = Nz(DMax("SequenceNum", "tbl_EstimateLog", "intYear = " & intMyYear), 0)  'searching for last seq number for current year
        
        Dim tReq As String
        Dim db As DAO.Database
        Dim Records As DAO.Recordset
        
        Dim strSQL As String
            Set db = CurrentDb
        tReq = "select tbl_Requests.txtprojectnumber from tbl_Requests " & _
                                        " where tbl_Requests.[txtProjectNumber] = '" & frm.txtProjectNumber.Value & "'"
        
            Set Records = db.OpenRecordset(tReq)
                If Records.RecordCount > 0 Then
                    Dim strLetter As String
                    Dim i As Integer
                        strLetter = "a"
                        For i = 1 To 26       'I PREVIOUSLY HAD 0 To 26 but returned the same result of "b"
                           strLetter = Chr(Asc(strLetter) + 1)
                    Next i
                    frm.SequenceNum.Value = intLastSeqNum & strLetter
            Else
                   frm.SequenceNum.Value = intLastSeqNum + 1
            End If
        End If

[Welshgasman]

That is because you add 1 to a starting value of asc of a?
So start on whatever character is before a in the ascii table

Not sure how you are going to progress to b ?

[davegri]

Key point is red line.

Option Compare Database
Option Explicit


Public Sub adv()
    Dim strLetter As String
    Dim i As Integer
        strLetter = Chr(96)
        For i = 1 To 26      'I PREVIOUSLY HAD 0 To 26 but returned the same result of "b"
           strLetter = Chr(Asc(strLetter) + 1)
           Debug.Print strLetter;
    Next i
End Sub

[Micron]

Could you ever do this with the folder structure ever being partially completed? If your code failed part way through, perhaps that would happen and next time you try to run it I would expect errors at least and maybe folder/files getting messed up at worst. I think I would loop from the Asc value +1 of the last value returned by DMax up to 122 if I was OK with being limited to 26 possibilities.

EDIT - I think I've misunderstood - the sequence number has nothing to do with the appended letter.