Avatar of EasyToHelp
EasyToHelp
Flag for South Africa asked on

Letter Incrimenting ID System

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

LotNumberPrefix
LotNumberId
LotNumberSuffix

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
Visual Basic.NET.NET ProgrammingC#

Avatar of undefined
Last Comment
LazyFolk

8/22/2022 - Mon
Nasir Razzaq

Have you started with the code? Would you use three separate textboxes for the three parts?
LazyFolk

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="A"
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
EasyToHelp

ASKER
I havent started with the code, and yes three text boxes that will be appended to one another later in the report.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Nasir Razzaq

@LazyFolk
>LotNumberSuffix="A"
>LotNumberSuffix = Chr(Asc(LotNumberSuffix) + 1)

What if
LotNumberSuffix="Z"
?

@EasyToHelp
Would suffix change to AA after Z?
adriankohws

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
        Else
            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.
LazyFolk

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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
EasyToHelp

ASKER
After Z it must go to AA and then go to BB then CC until ZZ then it will goto AAA
LazyFolk

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"
        Else
            suffix = getNextSuffix(Left(actualSuffix, Len(actualSuffix) - 1)) + "A"
        End If
    Else
        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)
        Next
    Next
End Sub

Open in new window


The attached screenshot show the result on my computer.
Result on my computer
EasyToHelp

ASKER
This is almost what i need!
I just cant have AB AC etc...

IT must be A B C D...Z AA BB CC DD.....ZZ AAA BBB CCC DDD EEE...ZZZ
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
ASKER CERTIFIED SOLUTION
LazyFolk

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question