Full Version: Alpha/Numeric Variation on DMax for Next Consecutive Number
UtterAccess Forums > Microsoft® Access > Access Forms
SparrowCathy
I use DMax + 1 often to get the next consecutive number for unique numbering schemes needed. They are not the primary key, I use autonumber for that and the user never sees those, they are simply to assign the next consecutive number in whatever scheme is needed. However, this one has an Alpha twist I'm not sure how to handle, and would appreciate any assistance.
The number scheme will start with set characters, and only the last 2 digits will increment.
Example XXXX01, XXXX02, XXXX03, etc. This will run 01 through 99. At 99, the scheme changes to A1 through Z9, and after Z9, it changes to AA through ZZ, so there are always only 2 digits added, the numeric never goes above 99. Front will always be XXXX, so that part is ok, was planning to create a concatenated final number adding the last 2 digits for every new record.
However, I have not handled any kind of incrementation with alpha characters. Any ideas of how to approach this would be appreciated, as I am just now setting up my tables and not sure yet what I need for this part, thanks.
Larry Larsen
Hi
Just a thought on a process..
ass the string into function "Larry123"
Split out text and numerics "Larry ---- 123"
Increment numeric eg:+1 "124"
Bolt them back together.. "Larry124"
just an idea..
thumbup.gif
cheekybuddha
You will also need a way to increment the alpha part
If you make sure they are in upper case once you split them from the numerics then you can test for their Asc() value.
Asc("A") = 65
Asc("Z") = 90
Chr(65) = A
Chr(90) = Z
hth,
d
cheekybuddha
Based on what you described, here's one way of doing it:
!--c1-->
CODE
Function fAlphaIncrement(strIn As String) As String
    Dim bNum As Byte, _
        bLen As Byte, _
        iPos As Integer, _
        strAlpha() As String, _
        strRet As String
    
[color="green"]'   Split alpha from numeric[/color]
    bNum = CByte(Right(strIn, 2))
    strRet = UCase(Left(strIn, Len(strIn) - 2))
[color="green"]'   Increment numeric portion and
'   test to see if less than 100[/color]
    If bNum + 1 < 100 Then
[color="green"]'       If so, just increment and join back up[/color]
        strRet = strRet & Format$((bNum + 1), "00")
    Else
[color="green"]'       otherwise, go through the letters
'       split into an array[/color]
        bLen = Len(strRet)
        ReDim strAlpha(1 To bLen)
        For iPos = 1 To bLen
            strAlpha(iPos) = Mid(strRet, iPos, 1)
        Next iPos
[color="green"]'       Loop through the array from 'right to left'[/color]
        For iPos = bLen To 1 Step -1
[color="green"]'           Test that letter '+1' is less than 'Z'[/color]
            If Asc(strAlpha(iPos)) + 1 < 91 Then
[color="green"]'               If so, increment and change in array and get out[/color]
                strAlpha(iPos) = Chr(Asc(strAlpha(iPos)) + 1)
                Exit For
            Else
[color="green"]'               otherwise, change to 'A'
'               and repeat for next letter to the left[/color]
                strAlpha(iPos) = "A"
            End If
        Next iPos
[color="green"]'       Put it all back together[/color]
        strRet = Join(strAlpha, "") & "01"
    End If
[color="green"]'   Return result[/color]
    fAlphaIncrement = strRet
    
End Function

Make sure you test it thouroughly - it's late here and it's kinda tricky!
d
Larry Larsen
Hi d
(one for the memory bank)
Pretty slick for a "night owl"..
thumbup.gif
cheekybuddha
Hi Larry,
Thanks for the kind words. frown.gif
My eyes were beginning to droop but it seemed like an interesting quandary and I had that niggling "How would I do that?" feeling!
Oknow it's better to guide the folk here rather than solve the problem for them (give a fish vs. teach to fish and all that) but since it took me 1/2 an hour I thought I'd post the solution I'd come up with anyway!
I should know better though because it meant that I overslept this morning blush.gif
thumbup.gif
d
cheekybuddha
Haha,
just looked at it again. It falls down when you get to the last alpha letter - it just rolls round again.
eg
ZZ99 --> AA01
instead of
ZZ99 --> AAA01
I have modified to handle that:
CODE
Function fAlphaIncrement(strIn As String) As String
    Dim bNum As Byte, _
        bLen As Byte, _
        iPos As Integer, _
        strAlpha() As String, _
        strRet As String, _
        blNew As Boolean
    
[color="green"]'   Split alpha from numeric[/color]
    bNum = CByte(Right(strIn, 2))
    strRet = UCase(Left(strIn, Len(strIn) - 2))
[color="green"]'   Increment numeric portion and
'   test to see if less than 100[/color]
    If bNum + 1 < 100 Then
[color="green"]'       If so, just increment and join back up[/color]
        strRet = strRet & Format$((bNum + 1), "00")
    Else
[color="green"]'       otherwise, go through the letters
'       split into an array[/color]
        bLen = Len(strRet)
        ReDim strAlpha(1 To bLen)
        For iPos = 1 To bLen
            strAlpha(iPos) = Mid(strRet, iPos, 1)
        Next iPos
[color="green"]'       Loop through the array from 'right to left'[/color]
        For iPos = bLen To 1 Step -1
[color="green"]'           Test that letter '+1' is less than 'Z'[/color]
            If Asc(strAlpha(iPos)) + 1 < 91 Then
[color="green"]'               If so, increment and change in array and get out[/color]
                strAlpha(iPos) = Chr(Asc(strAlpha(iPos)) + 1)
                Exit For
            Else
[color="green"]'               otherwise, change to 'A'
'               and repeat for next letter to the left[/color]
                strAlpha(iPos) = "A"
[color="green"]'               If this is the leftmost letter we need to
'               add another 'A' to the beginning[/color]
                blNew = (iPos = 1)
            End If
        Next iPos
[color="green"]'       Put it all back together[/color]
        strRet = Join(strAlpha, "") & "01"
[color="green"]'       Add new 'A' if required[/color]
        If blNew Then strRet = "A" & strRet
    End If
[color="green"]'   Return result[/color]
    fAlphaIncrement = strRet
    
End Function

hth,
d
cheekybuddha
OK, I know I should quit wilst I'm ahead! But here is a leaner version which removes the need for an array and half the variables! This is what I was trying to achieve last night but couldn't get my sleepy brain around blush.gif
!--c1-->
CODE
Function fAlphaIncrement2(ByVal strIn As String) As String
    Dim bNum As Byte, _
        bLen As Byte, _
        bPos As Byte
[color="green"]'   Split alpha from numeric[/color]
    bNum = CByte(Right(strIn, 2))
    strIn = UCase(Left(strIn, Len(strIn) - 2))
[color="green"]'   Increment numeric portion and
'   test to see if less than 100[/color]
    If bNum + 1 < 100 Then
[color="green"]'       If so, just increment and join back up[/color]
        strIn = strIn & Format$((bNum + 1), "00")
    Else
        bLen = Len(strIn)
[color="green"]'       Navigate through each
'       letter from right to left[/color]
        For bPos = 0 To bLen - 1
[color="green"]'           Increment letter[/color]
            If Asc(Mid(strIn, bLen - bPos, 1)) + 1 < 91 Then
                Mid(strIn, bLen - bPos, 1) = Chr(Asc(Mid(strIn, bLen - bPos, 1)) + 1)
                Exit For
            Else
[color="green"]'               Loop again if necessary[/color]
                Mid(strIn, bLen - bPos, 1) = "A"
            End If
        Next bPos
        strIn = strIn & "01"
[color="green"]'       Add extra 'A' if required[/color]
        If bPos = bLen Then strIn = "A" & strIn
    End If
    fAlphaIncrement2 = strIn
    
End Function

[/color]
SparrowCathy
d, thank you very much . . . sorry to cause oversleeping sad.gif
really appreciate the help and believe me, as far behind in learning as I am, I'm definately in the 'learning to fish' category! This will take some learning for me to put it all together and utilize the function, so keep me in your prayers! frown.gif
Thank you for the revision, too, that answers a question that was just starting to form, but I tabled it till I could start testing and learn more about how it works.
I'm excited about learning something new!
Thanks again, and thank you also, Larry, very much.
cheekybuddha
Hi SparrowCathy,
You're very welcome (and don't worry about the oversleeping - I was only late for myself!)
Oshould have asked how you plan to store your 'number'. I was assuming that you would store it in one field in your table. If so, you would use the function in a similar way to DMax() +1
For instance, in a textbox in your form bound to the field you could put as the Default Value:
=fAlphaIncrement2(Nz(DMax("AlphaField", "YourTable"), "A00"))
If you are planning to store the alpha part and numeric part separately then a slight re-think is required.
hth,
d
SparrowCathy
Thanks d, yes, I was planning to store it in one field in my table so I could use it later in other things. They need to generate the number, then it will be used in reports and exports to other systems that utilize the generated number. I don't see any reason to store them separately since my primary key will be an autonumber, but I can rethink that if needed . . .
Thanks for the explanation on the form side, that's very helpful. Appreciate your help very much, I'm looking forward to tackling this one!
Cheers!
cheekybuddha
Yes, I think it's probably easier to store it all in one field.
You can also create a 'wrapper' function to provide you with the next new number:
CODE
Function fNextAlphaNum() As String
    fNextAlphaNum = fAlphaIncrement2(Nz(DMax("AlphaField", "YourTable"), "A00"))
End Function

Replace the 'AlphaField' and 'YourTable' with the appropriate field and table names and whenever you need the next number just use:
in code:
Dim strNewNum as String
strNewNum = fNextAlphaNum
in control's default value:
=fNextAlphaNum
in query:
Insert Into YourTable (AlphaField, Field1, Field2) Values (fNextAlphNum(), 'Value1', 999);
etc...
hth,
d
SparrowCathy
Yes, d, thanks, that's exactly what I was needing, thank you very much. I'll let you know if I get myself into trouble putting this together . . . it can happen!
Hope your day is great and you get a chance for some extra rest!!
thumbup.gif
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.