Please help with alphanumeric number handling

[crazytegger]

I'm creating a function that will autoincrement a purchase order number. The number is the logged in users initials followed by four numbers (eg. JC0001). How would I go about determining the highest existing number with the prefix of the person who is creating the new order?

Thanks, Justin

Here is my code, I apologize if it is a bit elementary:

[CODE=vb]Public Function GeneratePONumbe r()
Dim MyInitials, MyName, MyNames
Dim strFname, strLname, strInitials As String
Dim intHighPO As Integer

'INITIAL GENERATOR
'Determines who is logged in, and generates user's initials
strFname = DLookup("userna me", "users", "userid = " & intAuthUserId)
strLname = DLookup("userla stname", "users", "userid = " & intAuthUserId)
MyNames = Split(strFname & " " & strLname, " ")
For Each MyName In MyNames
If Len(MyName) > 0 Then MyInitials = MyInitials & UCase(Left(MyNa me, 1))
Next
'END INITIAL GENERATOR

For Each OrderPONumber In orders
'FIND HIGHEST PO NUMBER BEGINNING WITH MyInitials
Next

'ADD INITIALS AND LAST PO NUMBER+1
strNewPONumber = MyInitials & intHighPO + 1

End Function[/CODE]

[Killer42]

I'd suggest you use the Max database function to find the maximum value that is
Like "JC*"
Or depending on the database, the wildcard character might be %, which would make:
Like "JC%"

You might also use a Between clause, such as
Between "JC0001" AND "JC9999"

[lotus18]

Hi

Make a composite key for that, combination of username and another key (set to autonumber)...

[Killer42]

Something I forgot to mention. This scheme looks as though has a fundamental flaw which may cause problems later. That is, the inability to handle multiple people with the same intials.

[9815402440]

hi

following code should help you
[CODE=vb]Const MyInitials = "JC"
Dim strArr(10) As String
Dim i As Integer
'Fill array
For i = 0 To 9
strArr(i) = MyInitials & Format(i, "0000#")
Next
Dim intMax As Integer
intMax = 0
'find maximum
For i = 0 To 9
If Val(Replace(str Arr(i), MyInitials, "")) > intMax Then
intMax = Val(Replace(str Arr(i), MyInitials, ""))
End If
Next
MsgBox MyInitials & Format(intMax + 1, "0000#")
[/CODE]
regards
manpreet singh dhillon hoshiarpur

[crazytegger]

Thanks for the help. I enjoy seeing how many different ways there are to accomplish the same objective with code. I thought about the composite key method however, I wanted to keep the numbers sequential per user. Also, thanks for pointing out the fundamental flaw, I'm still trying to decide how to address this. I will reply back when I figure it out.

Thanks again,

Justin

[Killer42]

Cool. I'm sure we'll all be interested to hear what direction you take.