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.

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.Text10))
    ' extract the last set of numbers
    vText = Mid(Trim(Me.Text10), 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.Text10), 1, 5) & "0" & vValString
    ' do what you want with the new string
    Me.Text12 = vValString
 

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:

"4WSO-" & Format$(Val(Right$(DLast("[Solar Lantern No]", "Beneficiary"), 2)) + 1, "00")

[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.

"4WSO-" & Format$(Val(Right$(DLast("[Solar Lantern No]", "Beneficiary"), 2)) + 1, "00")

[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.

"4WSO-" & Format$(Val(Right$(DLast("[Solar Lantern No]", "Beneficiary"), 2)) + 1, "00")

[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.