Letter Incrimenting ID System

EasyToHelp
EasyToHelp used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

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

Commented:
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

Author

Commented:
I havent started with the code, and yes three text boxes that will be appended to one another later in the report.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Most Valuable Expert 2012
Top Expert 2014

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

What if
LotNumberSuffix="Z"
?

@EasyToHelp
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
        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.

Commented:
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

Author

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

Commented:
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

Author

Commented:
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
Commented:
Then replace the getNextSuffix function with this one :

Function getNextSuffix(actualSuffix) As String
    Dim suffix, nextSuffix As String
    Dim suffixSize As Integer
    
    nextSuffix = ""
    suffix = Right(actualSuffix, 1)
    suffixSize = Len(actualSuffix)
    
    If suffix = "Z" Then
        suffix = "A"
        suffixSize = suffixSize + 1
    Else
        suffix = Chr(Asc(Right(actualSuffix, 1)) + 1)
    End If
    For i = 1 To suffixSize
        nextSuffix = nextSuffix + suffix
    Next
    getNextSuffix = nextSuffix
End Function

Open in new window

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial