alphabet incremental counter function

[paccatore]

Hello, sorry but i'm a poor newbie...
well... I'm searching for an (Anglo-Saxon) alphabet incremental counter function doing something like this:

starting counter: MAAA
+ 1 = MAAB, then MAAC and so on
until MAAZ and after this
MAA0, MAA1..., MAA9
and then MBAA, MBAB, MBAC.... until M999 and then NAAA and so on... till the last one: Z999



any help will be appreciated!
thanks

[ranman256]

Instead of making some complicated numbering system using letters and numbers,
make it simple by just using a counter autonumber field. NO programming, NO upkeep.

But If you need text code involved you could add 1 field:
[code], [counter]
MBAA, 9453

I dont see the need for this complex 'counter'.

[paccatore]

Thank you very much ranman for the reply!
Really I still don't understand the need for thi sophisticated calculation...
But SDA vector gived to me this directive for box univocal labels numbering
How can I solve this trouble? What do you mean? How can I extract what you named [code]?

[orange]

As ranman has suggested, I think the use of autonumber is the approach that would be followed by most.
However, it isn't clear why you need the incremental alpha set up. Perhaps you are confusing WHAT you need to accomplish and HOW it could be done.
Autonumbers are assigned by the database system to uniquely identify records.
If you need some corresponding value for use by humans, then that is a separate issue and can be resolved.

We need more details on What you are trying to accomplish in business terms. And once readers understand WHAT, they will offer advice/options for the HOW.
Good luck.

[paccatore]

Ok, here the SDA Specifications for multiple boxes ID label generation:

From 5th to 8th position (alphanumeric characters)
Progressive alphabet as follows:
5th Character: variable M to Z
6th Character: variable to Z and 0 (zero) to 9
7th Character: variable to Z and 0 (zero) to 9
8th Character: variable to Z and 0 (zero) to 9
The alphabet used for this numbering is the Anglo-Saxon

thank you orange

[June7]

I have assisted with similar requirement. Had to convert character to ASCII code equivalent, increment by adding 1 then convert back to character.

Asc("A")

returns 65

so

Chr(Asc("A") + 1)

returns B

Use string manipulation functions to parse string characters and calculate. Need a VBA custom function. See if this does what you want:

Function GetNext(strCode As String)
Dim strX As String, x As Integer, aryX(4) As String
If strCode = "Z999" Then
    GetNext = "Max"
    Exit Function
Else
    For x = 1 To 4
        aryX(x - 1) = Mid(strCode, x, 1)
    Next
    If aryX(3) = "9" Then
        aryX(3) = "A"
    Else
        aryX(3) = IIf(aryX(3) = "Z", 0, Chr(Asc(aryX(3)) + 1))
        GoTo ExitProc
    End If
    If aryX(2) = "9" Then
        aryX(2) = "A"
    Else
        aryX(2) = IIf(aryX(2) = "Z", 0, Chr(Asc(aryX(2)) + 1))
        GoTo ExitProc
    End If
    If aryX(1) = "9" Then
        aryX(1) = "A"
    Else
        aryX(1) = IIf(aryX(1) = "Z", 0, Chr(Asc(aryX(1)) + 1))
        GoTo ExitProc
    End If
    If aryX(0) <> "Z" Then aryX(0) = Chr(Asc(aryX(0)) + 1)
    End If
End If
ExitProc:
GetNext = aryX(0) & aryX(1) & aryX(2) & aryX(3)
End Function

GoTo is not most elegant programming structure but best I could get.

But maybe the trickiest part is programmatically retrieving the most recent existing Code from table. Your sequencing does not fit with alpha sort rules. For example MAAA sorts after M999. Probably need another even more complicated function.


What is SDA?

[orange]

Can you address this?

We need more details on What you are trying to accomplish in business terms. And once readers understand WHAT, they will offer advice/options for the HOW.

What will this code be used for in the operational database? You have shown us some info about the 5th to 8th characters of a code, but no context of the business requirements for the database; no business rules; no data model. We're trying to help, but we need something to work with.

[paccatore]

Hello guys! thanking you for the help, I apologize (not only for the low-skilled) for the very bad english (understood and writted) and for the shortage of information, ...
What I'm trying to do is a Microsoft Access VBA program used by warehousemen to print labels for packages to ship with SDA express courier (a competitor of DHL, you know...).
Meanwhile I got more detailed information from the vector, about the sequential barcode128 to print in the labels: there isn't a specific mandatory order of letters and numbers and holes are permitted, but obviously without duplicates.
This means that I can write more comfortably: start M000, M009, M00A, M00Z, M0A0..
I'll try your solution June, thanks!

[paccatore]

THANK YOU June7, Your code works great! Although I don't understand it very much ... would you be kind enough to explain me in detail how it works?

[paccatore]

I thought the world was a worse place, then I happened upon this forum and saw that there are still people willing to help others, free of charge. MANY THANKS! I owe you!

[orange]

about the sequential barcode128

http://courses.cs.washington.edu/cou.../barcodes.html

What do you use now?
Have you considered purchasing an Off the shelf (OTS) proven product?

[June7]

I have to let the logic of the code explain itself. Follow the If Then Else execution, picture it in your head or draw out a logic diagram with pen and paper. The procedure declares an array object (if you need to learn about arrays then search VBA help or web) and populates it with the letters of the code then manipulates the array elements.

If the code sequence can follow normal alpha sort, then maybe pulling the most recent code can be done with DMax and the procedure can be altered.

Function GetNext() As String
Dim strCode As String, strX As String, x As Integer, aryX(4) As String

strCode = Nz(DMax("Code", "Tablename"),"")

If strCode = "" Then
'to accommodate empty db and generate first Code
GetNext = "M000"
Exit Function
Else
If strCode = "ZZZZ" Then
...

End Function

[paccatore]

orange, for the code128 i'm trying this function:

Option Compare Database

' Written by Rodney Marr (RodMarr@mailcity.com) October 7, 2000
'
' Barcode 128-B Generator
'
' Permission granted for public use and royalty-free distribution.
' No mention of source or credits is required.
'
' I got a lot of help from the following people's work
' Russ Adams' BarCode 1 Web Page   http://www.adams1.com/pub/russadam/info.html
' A Free 128-B code generator in Visual Basic by Stefan Karlsson (mrswede@libertysurf.se)
' And the Creator of the code 39 Module
'
'For Notes on how to use this code look at the code for the barcode39 Generator

Public Function Barcode_128(Ctrl As Control, rpt As Report)
    'On Error GoTo ErrorTrap_Barcode_128

    'Code 128B has 5 main parts to it. The first part is a start character(211214), followed by DataCharcters. The Data
    'Characters are followed by a check(or Checksum) Character, and that is followed by a stop Character(2331112)
    'The last part of Code 128B is the two quiet sections at the front and back of the barcode. These are 10 dimensions
    'Long(I am thinking that is 11 modules long). Each character is 11 modules long, except the stop character which is
    '13 modules long

    Dim CharNumber As Variant, CharData As Variant, CharBarData As Variant, Nratio As Variant, Nbar As Variant
    Dim barcodestr As String, Barcode As Control, Barchar As String, Barcolor As Long, Parts As Integer, J As Integer
    Dim tsum As Integer, lop As Integer, s As Integer, checksum As Integer, p As Integer, barwidth As Integer
    Dim boxh As Single, boxw As Single, boxx As Single, boxy As Single, Pix As Single, Nextbar As Single
    Const White = 16777215: Const Black = 0

    'This is the Raw data that I threw into an arrays
    CharNumber = Array("0", "1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16,", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29,", "30", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47", "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61", "62", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75", "76", "77", "78", "79", "80", "81", "82", "83", "84", "85", "86", "87", "88", "89", "90", "91", "92", "93", "94", "95", "96", "97", "98", "99", "100", "101", "102", "103", "104", "105", "106")
    CharData = Array("SP", "!", Chr(34), "#", "$", "%", "&", "'", "(", ")", "*", "+", ",", "-", ".", "/", "0", "1", "2", "3", "4", "5", "6", "7", "8", "9", ":", ";", "<", "=", ">", "?", "@", "A", "B", "C", "D", "E", "F", "G", "H", "I", "J", "K", "L", "M", "N", "O", "P", "Q", "R", "S", "T", "U", "V", "W", "X", "Y", "Z", "[", "\", "]", "^", "_", "`", "a", "b", "c", "d", "e", "f", "g", "h", "i", "j", "k", "I", "m", "n", "o", "p", "q", "r", "s", "t", "u", "v", "w", "x", "y", "z", "{", "|", "}", "~", "DEL", "FNC 3", "FNC 2", "SHIFT", "CODE C", "FNC 4", "CODE A", "FNC 1", "Start A", "Start B", "Start C", "Stop")
    CharBarData = Array("212222", "222122", "222221", "121223", "121322", "131222", "122213", "122312", "132212", "221213", "221312", "231212", "112232", "122132", "122231", "113222", "123122", "123221", "223211", "221132", "221231", "213212", "223112", "312131", "311222", "321122", "321221", "312212", "322112", "322211", "212123", "212321", "232121", "111323", "131123", "131321", "112313", "132113", "132311", "211313", "231113", "231311", "112133", "112331", "132131", "113123", "113321", "133121", "313121", "211331", "231131", "213113", "213311", "213131", "311123", "311321", "331121", "312113", "312311", "332111", "314111", "221411", "431111", "111224", "111422", "121124", "121421", "141122", "141221", "112214", "112412", "122114", "122411", "142112", "142211", "241211", "221114", "413111", "241112", "134111", "111242", "121142", "121241", "114212", "124112", "124211", "411212", "421112", "421211", "212141", _
                        "214121", "412121", "111143", "111341", "131141", "114113", "114311", "411113", "411311", "113141", "114131", "311141", "411131", "211412", "211214", "211232", "2331112")

    barcodestr = "211214" 'Add the Startcode for Start B (characterset B) to the barcode string
    tsum = 104                      'And this is the value for that startcode which will be added with the other character values to find the checksum character
    boxx = Ctrl.Left: boxy = Ctrl.TOP: boxw = Ctrl.Width: boxh = Ctrl.Height    'Get control size and location properties.

    Set Barcode = Ctrl                                                          'Set handle on control (su access dopo 2003 anche senza set ma con BarCode as String)

    Nratio = Array("0", "15", "30", "45", "60")           'Set up the array for the different bar width ratios
    Parts = ((11 * (Len(Barcode))) + 35) * Nratio(1)  'This is the formula for the width of the barcode
    Pix = (boxw / Parts)                                                  'Here I find out exactly how many Pixels a bar will be
    Nbar = Array((Nratio(0) * Pix), (Nratio(1) * Pix), (Nratio(2) * Pix), (Nratio(3) * Pix), (Nratio(4) * Pix)) 'Set up the array to handle the pixels for each type of bar

    'Loop through all bardata to count the sum for all characters and add barcode charcter strings to the barcode string
    For lop = 1 To Len(Barcode)
        Barchar = Mid(Barcode, lop, 1)
        If Barchar = " " Then Barchar = "SP"
        For s = 0 To UBound(CharData)
            If Barchar = CharData(s) Then
                barcodestr = barcodestr & CharBarData(s) 'This is where I added the character strings to each other to make one long string of 1's, 2's, 3's, & 4's
                tsum = tsum + (CLng(CharNumber(s)) * lop) 'Here every barcode character's number value is multiplied by its position in the line and added to tsum
                'The actual formula for find the the  Checksum  is "(104 + (1 * CharcterNumber) + (2 * CharcterNumber) + ...)/103" You would Use the Remainder as
                'The Checksum Character. In the case of "BarCode 1" the formula would look
                'like "(104+(1*34)+(2*65)+(3*82)+(4*35)+(5*79)+(6*68)+(7*69)+(8*0)+(9*17))/103=20 with Remainder of 33" Therefore the checksum would equal 33
                Exit For
            End If
        Next s
    Next lop

    checksum = tsum - (Int(tsum / 103) * 103)                                             'Here I use the the totat sum (tsum) to find the checksum
    barcodestr = barcodestr & CharBarData(checksum) & "2331112" 'Here I add the checksum then the stop character into the barcode string

    'lets do some initialization
    Barcolor = Black
    Nextbar = boxx + 11     'I added the 20 for the whitespace (or quiet space) at the beginning of the barcode

    'Draw the Barcode
    For J = 1 To Len(barcodestr)
        Barchar = Mid(barcodestr, J, 1)   'Reuse variable barchar to store the character to be drawn
        barwidth = CInt(Barchar)              'Change the barcode charcter into an integer so I can use in the array part of the next line
        rpt.Line (Nextbar, boxy)-Step(Nbar(barwidth), boxh), Barcolor, BF  'Draw the line
        Nextbar = Nextbar + Nbar(barwidth)                                                      'Calculate the next starting point
        If Barcolor = White Then Barcolor = Black Else Barcolor = White      'Swap line colors
    Next J

Exit_Barcode_128:
    Exit Function

ErrorTrap_Barcode_128:
    MsgBox Error$

    Resume Exit_Barcode_128
End Function

[June7]

Please post code within CODE tags to retain indentation and make it more readable.

[orange]

As June said, you should use code tags around your vba code when posting.

Also

orange, for the code128 i'm trying this function:

Does it work? If not, what is are the issue(s)?