Identify next sequence

[Robie]

Hi

If I get strings such as "14.3.2c", "14.3.3", etc. is it possible to workout the next sequence from the original string? For example,

if 14.3.2c comes in then the next must be 14.3.2d or
if 14.3.3 comes in then the next must be 14.3.3a or
if 14.3.3z comes in then the next must be 14.3.4

Thanks.
Robie

[HansV]

Do you want to do this using VBA? Or using field codes?

[Robie]

Do you want to do this using VBA? Or using field codes?

VBA please.
Thanks.

[HansV]

Here is a function you can use:

Code: Select all

Function GetNext(seq As String) As String
    Dim strLeft As String
    Dim strRight As String
    Dim lngPos As Long
    strLeft = Left(seq, Len(seq) - 1)
    strRight = Right(seq, 1)
    Select Case strRight
        Case "0" To "9"
            GetNext = seq & "a"
        Case "z"
            lngPos = InStrRev(strLeft, ".")
            If lngPos = 0 Then
                GetNext = strLeft + 1
            Else
                GetNext = Left(strLeft, lngPos) & (Mid(strLeft, lngPos + 1) + 1)
            End If
        Case Else
            GetNext = strLeft & Chr(Asc(strRight) + 1)
    End Select
End Function

Example of usage:

Code: Select all

    Dim strOld As String
    Dim strNew As String
    strOld = "14.3.2a"
    strNew = GetNext(strOld)
    Debug.Print strNew

[Robie]

Here is a function you can use:

Code: Select all

Function GetNext(seq As String) As String
    Dim strLeft As String
    Dim strRight As String
    Dim lngPos As Long
    strLeft = Left(seq, Len(seq) - 1)
    strRight = Right(seq, 1)
    Select Case strRight
        Case "0" To "9"
            GetNext = seq & "a"
        Case "z"
            lngPos = InStrRev(strLeft, ".")
            If lngPos = 0 Then
                GetNext = strLeft + 1
            Else
                GetNext = Left(strLeft, lngPos) & (Mid(strLeft, lngPos + 1) + 1)
            End If
        Case Else
            GetNext = strLeft & Chr(Asc(strRight) + 1)
    End Select
End Function

Example of usage:

Code: Select all

    Dim strOld As String
    Dim strNew As String
    strOld = "14.3.2a"
    strNew = GetNext(strOld)
    Debug.Print strNew

Wow Hans.

That is fantastic. Works wonderfully well. I thought it would be such a huge task as I just couldn't get my head around it.

What can I say? A big THANKS.