Link to home
Start Free TrialLog in
Avatar of EasyToHelp
EasyToHelpFlag for South Africa

asked on

Letter Incrimenting ID System

I have a product ID system that works on letters and numbers


If a member puts in a LotNumberPrefix which can be A-Z it must insert the next available LotNumberId which is linked to the prefix. EG.

LotNumberPrefix | LotNumberId | LotNumberSuffix
A | 1
A | 2
A | 3
B | 1
B | 6

If they enter a LotNumberPrefix  and a LotNumberId Manually that exists it must add a LotNumberSuffix automatically Starting with A and incrimenting Like This:

LotNumberPrefix | LotNumberId | LotNumberSuffix
A | 1
A | 2
A | 2 | A
A | 2 | B
A | 2 | C
Avatar of Nasir Razzaq
Nasir Razzaq
Flag of United Kingdom of Great Britain and Northern Ireland image

Have you started with the code? Would you use three separate textboxes for the three parts?
what is the question exactly?

Increment character ?
in vb ?

you can use function ASC to convert a letter to its ASCII representation (A = 65, B=66,...) that you can easily increment.
The CHR function will do the opposite conversion (CHR(65) = A)

the following code will increment "LotNumberSuffix" from A to B

LotNumberSuffix = Chr(Asc(LotNumberSuffix) + 1)

Open in new window

When arrived to Z (ASC(Z)=90) you have to figure out how to stop incrementing
Avatar of EasyToHelp


I havent started with the code, and yes three text boxes that will be appended to one another later in the report.
>LotNumberSuffix = Chr(Asc(LotNumberSuffix) + 1)

What if

Would suffix change to AA after Z?
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim Result As Integer = Asc(TextBox1.Text)
        If Result = 90 Then
            Result += 7
            Result += 1
        End If
        TextBox2.Text = Chr(Result).ToString
    End Sub

The above will change A to Z, when reaches Z changed to a through z.

But the point is, you are trying to have an unqiue ID of a transaction which I think it's not
supposed to be done this way, it's messy and painful.

For me, for your case, I always auto generate the ID. I used a table of two columns, Prefix and RunningNumber column to Keep the data. Every time a new ID is generated, I update the RunningNumber column.

Or at least, when a user attempts to insert an existing number, you try to get the max value of that number and advise the user instead of adding suffix.
as I said in the post :

When arrived to Z (ASC(Z)=90) you have to figure out how to stop incrementing

No rule has been defined for the case where suffix is bigger than "Z".
Therefore I cannot help implementing it

You can
- continue with lower case
- Adding another suffix char
- Adding a number
- Forbid increment
After Z it must go to AA and then go to BB then CC until ZZ then it will goto AAA
sorry for the delay, I was travelling.
Here is a recursive function that achieve the following count :

A -> ... -> Z -> AA -> ... -> AZ -> BA -> ... -> BZ -> ... -> ZZ -> AAA -> ...

Function getNextSuffix(actualSuffix) As String
    Dim suffix As String
    suffix = actualSuffix
    If Right(actualSuffix, 1) = "Z" Then
        If Len(actualSuffix) = 1 Then
            suffix = "AA"
            suffix = getNextSuffix(Left(actualSuffix, Len(actualSuffix) - 1)) + "A"
        End If
        suffix = Left(actualSuffix, Len(actualSuffix) - 1) + Chr(Asc(Right(actualSuffix, 1)) + 1)
    End If
    getNextSuffix = suffix
End Function

Open in new window

you can easily test this function with the sample code below (copy paste sub and function in excel vba and run it)

Sub test()
    Dim mySuffix As String
    Dim nextSuffix As String
    mySuffix = "A"
    For i = 1 To 28
        For j = 1 To 26
            Cells(i, j).Value = mySuffix
            mySuffix = getNextSuffix(mySuffix)
End Sub

Open in new window

The attached screenshot show the result on my computer.
User generated image
This is almost what i need!
I just cant have AB AC etc...

Avatar of LazyFolk
Flag of Switzerland image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial