Primary Key

[sajitk]

Dear Friends,

I have a primary Key called the Solar Lantern No in a table called Beneficiary. The number has a format, namely, 4WS0-01; 02; 03 etc. the solar lantern no starts with 4WS0. how do we do this in Access . the Solar Lantern No increases on its own....

the field in the recordset Beneficiary are:

solar lantern no, PK
Name, Text
Motivator, Text
cost, Numeric

looking forward to hear from you.
Thanks
Sajit

[mshmyob]

Your question was vague but I assume you want to increment the last part of your string by 1 each time based on a trigger.

Here is the code.

[code=vb]
Dim vLng As Long
Dim vValString As String
Dim vText As String
Dim vLen As Long

' get the length of the string
vLen = Len(Trim(Me.Tex t10))
' extract the last set of numbers
vText = Mid(Trim(Me.Tex t10), 6, vLen - 5)
'convert the number string to numeric
vLng = Val(vText)
' increment the value by 1
vLng = vLng + 1
' convert the number back to a string
vValString = CStr(vLng)
' conecate the original string prefix and the new numeric string
vValString = Mid(Trim(Me.Tex t10), 1, 5) & "0" & vValString
' do what you want with the new string
Me.Text12 = vValString
[/code]

cheers,

Dear Friends,

I have a primary Key called the Solar Lantern No in a table called Beneficiary. The number has a format, namely, 4WS0-01; 02; 03 etc. the solar lantern no starts with 4WS0. how do we do this in Access . the Solar Lantern No increases on its own....

the field in the recordset Beneficiary are:

solar lantern no, PK
Name, Text
Motivator, Text
cost, Numeric

looking forward to hear from you.
Thanks
Sajit

[ADezii]

Dear Friends,

I have a primary Key called the Solar Lantern No in a table called Beneficiary. The number has a format, namely, 4WS0-01; 02; 03 etc. the solar lantern no starts with 4WS0. how do we do this in Access . the Solar Lantern No increases on its own....

the field in the recordset Beneficiary are:

solar lantern no, PK
Name, Text
Motivator, Text
cost, Numeric

looking forward to hear from you.
Thanks
Sajit

The following code will produce the next [Solar Lantern No] in sequence:
[CODE=vb]
"4WSO-" & Format$(Val(Rig ht$(DLast("[Solar Lantern No]", "Beneficiar y"), 2)) + 1, "00")[/CODE]

[ADezii]

Dear Friends,

I have a primary Key called the Solar Lantern No in a table called Beneficiary. The number has a format, namely, 4WS0-01; 02; 03 etc. the solar lantern no starts with 4WS0. how do we do this in Access . the Solar Lantern No increases on its own....

the field in the recordset Beneficiary are:

solar lantern no, PK
Name, Text
Motivator, Text
cost, Numeric

looking forward to hear from you.
Thanks
Sajit

The following code will produce the next [Solar Lantern No] in sequence, in the format 4WSO-00:

1. Retrieve the Last [Solar Lantern No].
2. Extract the last 2 values.
3. Increment it by 1.
4. Reformat the Number "00".
5. Append it to the Base String.
6. NOTE: Logic only valid up to 4WSO-99.

[CODE=vb]
"4WSO-" & Format$(Val(Rig ht$(DLast("[Solar Lantern No]", "Beneficiar y"), 2)) + 1, "00")[/CODE]

[mshmyob]

Both code will work. The only difference is my has no limit and I kept the leading 0 for you (easy to remove if you want). I broke it out so the logic should be easy to understand. Adezi has spelled out the logic nicely.

cheers,

The following code will produce the next [Solar Lantern No] in sequence, in the format 4WSO-00:

1. Retrieve the Last [Solar Lantern No].
2. Extract the last 2 values.
3. Increment it by 1.
4. Reformat the Number "00".
5. Append it to the Base String.
6. NOTE: Logic only valid up to 4WSO-99.

[CODE=vb]
"4WSO-" & Format$(Val(Rig ht$(DLast("[Solar Lantern No]", "Beneficiar y"), 2)) + 1, "00")[/CODE]

[ADezii]

Both code will work. The only difference is my has no limit and I kept the leading 0 for you (easy to remove if you want). I broke it out so the logic should be easy to understand. Adezi has spelled out the logic nicely.

cheers,

The only difference is my has no limit and I kept the leading 0 for you (easy to remove if you want).

If I am not mistaking you, you simply cannot remove the leading 0 and expect to generate the next number in sequence. If there is no limit to the numbering system then perhaps it needs to be reformatted to:4WSO-000000 which would allow values from 4WSO-000001 to 4WSO-999999 inclusive.

[mshmyob]

I kept it the way he orginally had it so the results would be

4WSO-01
4WSO-02
.
.
4WSO-010
4WSO-011
.
etc.

I don't see a problem except the limitation of the field length he has set.

If he wants to drop the 0 after it reaches 10 then he just need to put an IF statement that looks to see if the new number is greater than 9 then drop the leading 0.

cheers,

If I am not mistaking you, you simply cannot remove the leading 0 and expect to generate the next number in sequence. If there is no limit to the numbering system then perhaps it needs to be reformatted to:4WSO-000000 which would allow values from 4WSO-000001 to 4WSO-999999 inclusive.