[RESOLVED] Increment Alphanumeric

[jelopy15]

I have a problem putting letter in my transaction number that is connected to mysql
i need a output like this "CS + date + number incrementation" (CS20110001)

here is my code

txttransnum.Text = Format(Date, "yyyy") & "000" & Val(id) + 1

i dont know how to put the letter "CS"

[dglienna]

That isn't correct. Try this:

at the top:

Dim IdNum as Integer
IdNum = 0

then, this code

IdNum = IdNum + 1
txttransnum.Text = "CS" & Format(Date, "yyyy") & "000" & Val(IdNum)

[DataMiser]

actually that should be CSTR(IdNum) or CSTR$(IdNum)
since the variable is an integer VAL() is pointless here.

also if you want it to end up as a 4 digit number then you should use either Format() or Right$()

IdNum = IdNum + 1
txttransnum.Text = "CS" & Format(Date, "yyyy") & Right$("000" & CSTR((IdNum)),4)

This way when your ID reaches 10 you will get 0010 instead of 00010

[dglienna]

Doesn't CSTR() pad the number with a leading space? As I recall... Haven't reloaded VB6 since May format

[jelopy15]

oh. sorry for the incomplete code. here is my whole code in my incrementation.

RSconn "select * from tblemprec order by Employee_Number asc"
If rs.EOF = False Then
For ctr = 0 To rs.RecordCount - 1
tempid = Right(rs(0), Len(rs(0)) - 4)
If Val(tempid) > Val(id) Then
id = tempid
End If

rs.MoveNext
If rs.EOF Then
Exit For

End If

Next

holdid = Val(id)

If Len(holdid) = 1 Then
txttransnum.Text = Format(Date, "yyyy") & "000" & Val(id) + 1

ElseIf Len(holdid) = 2 Then
txttransnum.Text = Format(Date, "yyyy") & "00" & Val(id) + 1

ElseIf Len(holdid) = 3 Then
txttransnum.Text = Format(Date, "yyyy") & "0" & Val(id) + 1

ElseIf Len(holdid) = 4 Then
txttransnum.Text = Format(Date, "yyyy") & Val(id) + 1

Else

txttransnum.Text = Format(Date, "yyyy") & "0001"
End If
End If

[dglienna]

Why? You are putting it into ASCENDING ORDER already.

What are you trying to do with the number?

[jelopy15]

So, this what i did. i copied your code and changed IDnum to id
and still..it doesnt increment. my code increments wnere is there is no alphabet.

RSconn "select * from tblemprec order by Employee_Number asc"
If rs.EOF = False Then
For ctr = 0 To rs.RecordCount - 1
tempid = Right(rs(0), Len(rs(0)) - 4)
If Val(tempid) > Val(id) Then
id = tempid
End If

rs.MoveNext
If rs.EOF Then
Exit For

End If

Next

holdid = Val(id)

If Len(holdid) = 1 Then
'txttransnum.Text = Format(Date, "yyyy") & "000" & Val(id) + 1

txttransnum.Text = "CS" & Format(Date, "yyyy") & Right$("000" & CStr((id)), 4)

ElseIf Len(holdid) = 2 Then
'txttransnum.Text = Format(Date, "yyyy") & "00" & Val(id) + 1
txttransnum.Text = "CS" & Format(Date, "yyyy") & Right$("00" & CStr((id)), 4)

ElseIf Len(holdid) = 3 Then
'txttransnum.Text = Format(Date, "yyyy") & "0" & Val(id) + 1
txttransnum.Text = "CS" & Format(Date, "yyyy") & Right$("0" & CStr((id)), 4)

ElseIf Len(holdid) = 4 Then
'txttransnum.Text = Format(Date, "yyyy") & Val(id) + 1
txttransnum.Text = "CS" & Format(Date, "yyyy") & Right$(CStr((id)), 4)
Else

'txttransnum.Text = Format(Date, "yyyy") & "0001"
txttransnum.Text = "CS" & Format(Date, "yyyy") & Right$("0001" & CStr((id)), 4)
End If
End If

[jelopy15]

Why? You are putting it into ASCENDING ORDER already.

What are you trying to do with the number?

so that it will look for my database in order, for example my last number is 20110004 in my database.then it will increment into 5.

[DataMiser]

holdid = Val(id)

If Len(holdid) = 1 Then
'txttransnum.Text = Format(Date, "yyyy") & "000" & Val(id) + 1

txttransnum.Text = "CS" & Format(Date, "yyyy") & Right$("000" & CStr((id)), 4)

ElseIf Len(holdid) = 2 Then
'txttransnum.Text = Format(Date, "yyyy") & "00" & Val(id) + 1
txttransnum.Text = "CS" & Format(Date, "yyyy") & Right$("00" & CStr((id)), 4)

ElseIf Len(holdid) = 3 Then
'txttransnum.Text = Format(Date, "yyyy") & "0" & Val(id) + 1
txttransnum.Text = "CS" & Format(Date, "yyyy") & Right$("0" & CStr((id)), 4)

ElseIf Len(holdid) = 4 Then
'txttransnum.Text = Format(Date, "yyyy") & Val(id) + 1
txttransnum.Text = "CS" & Format(Date, "yyyy") & Right$(CStr((id)), 4)

There is no need to check the len of the ID

The statement

NewString=Right$("000" & Cstr(MyInt),4)

will give you the right most 4 characters of the resulting string

so
0001 would be 0001
00010 would be 0010
000100 would be 0100
0001000 would be 1000


@dglienna

Doesn't CSTR() pad the number with a leading space? As I recall... Haven't reloaded VB6 since May format

No the older STR$() function allows a space for the +/- sign which shows as a space in the case of positive numbers. CSTR() strips the leading space.

[dglienna]

It was a problem in 2006. Had to merge to a .DOC file. Had to TRIM()

[DataMiser]

I've been using CSTR() for the last 15 years or so and have never saw an issue. VBDOS VB3,4,5 and 6 with all the various service packs along the way and it has always worked the same as it does now.

STR$() is another matter

The following code will produce a string without a leading space and one with a leading space.

Dim x As Integer
x = 15

Debug.Print "'" & CStr(x) & "'   '" & Str(x) & "'"

Results from debug window from code above are
'15' ' 15'

Now this is in VB6 SP6 but the result will be exactly the same with any SP as they also will in VB5 any SP, VBA, VBScript and any other version of MS Basic which contains the CSTR() function

[dglienna]

That's right. Had them reversed... (Not going to open my program in NOTEPAD)