I have a ID eg. A-00454 I want to increase it by one. After increment the ID should contain 5 digits in the number portion.
I have a ID eg. A-00454 I want to increase it by one. After increment the ID should contain 5 digits in the number portion.
sounds like you require what is frequently called 'dmax+1'. google/bing to find out more. If you require further help, you will need to explain what happens with the 'A' - is it always A, can be a different letter, or more than one letter. Also need to explain how you increment - is this manual entry so records are added one at a time - or copy/paste or import multiple records at a time. And whether it is
A1
A2
A3
B1
B2
B3
or
A1
A2
B3
A4
B5
B6
In principle you will need to isolate the numeric part of the id and make it numeric and add 1 e.g.
val(dmax("mid(myfield),3)","myTable","perhaps some criteria here for the alpha part of the ID"))+1
However note that it is usually better to store the ID in its component parts and correct datatype - much easier to maintain and generally faster operation - and combine as and when required - e.g.
alphacode & "-" & format(numericcode,"00000")
I tried with the following code,sounds like you require what is frequently called 'dmax+1'. google/bing to find out more. If you require further help, you will need to explain what happens with the 'A' - is it always A, can be a different letter, or more than one letter. Also need to explain how you increment - is this manual entry so records are added one at a time - or copy/paste or import multiple records at a time. And whether it is
A1
A2
A3
B1
B2
B3
or
A1
A2
B3
A4
B5
B6
In principle you will need to isolate the numeric part of the id and make it numeric and add 1 e.g.
val(dmax("mid(myfield),3)","myTable","perhaps some criteria here for the alpha part of the ID"))+1
However note that it is usually better to store the ID in its component parts and correct datatype - much easier to maintain and generally faster operation - and combine as and when required - e.g.
alphacode & "-" & format(numericcode,"00000")
Dim num As String
Dim fnum As String
Dim lnum As String
num = Me.List174
fnum = Right(num, Len(num) - InStr(num, "-"))
fnum = fnum + 1
Do While Len(fnum) < 5
fnum = "0" + fnum
Loop
lnum = "I-" + fnum
Me.CR_NO = lnum
Exit Sub
Err_code:
MsgBox Error$
Is val() required?
Estimate the reasonable max number of digits your ID can ever have (otherwise you can never order your data by ID, as older and newer entries will be messed up). And remember, better you'll have an additional "0" in your ID, than you'll have to update all your tables containing values of ID with this additional "0" someday in future. In your example in opening post it is 7, so I'll continue with it.
Code:Me.CR_NO = LEFT(Me.List174,LEN(Me.List174)-7) & RIGHT("0000000" & CStr(CLng(Right(Me.List174, 7))+1),7)
I tried with the following code,Estimate the reasonable max number of digits your ID can ever have (otherwise you can never order your data by ID, as older and newer entries will be messed up). And remember, better you'll have an additional "0" in your ID, than you'll have to update all your tables containing values of ID with this additional "0" someday in future. In your example in opening post it is 7, so I'll continue with it.
Code:Me.CR_NO = LEFT(Me.List174,LEN(Me.List174)-7) & RIGHT("0000000" & CStr(CLng(Right(Me.List174, 7))+1),7)
Dim num As String
Dim fnum As String
Dim lnum As String
num = Me.List174
fnum = Right(num, Len(num) - InStr(num, "-"))
fnum = fnum + 1
Do While Len(fnum) < 5
fnum = "0" + fnum
Loop
lnum = "I-" + fnum
Me.CR_NO = lnum
Exit Sub
Err_code:
MsgBox Error$
Is val() required?
On based on this (your answer to Ajax) was my post.
1. Why have a long procedure when a single formula will do?
2. "fnum = fnum + 1" You are adding 1 to string! It will not work in Access (but it will work in Excel)!
3. I used CLng() - it converts string to ńumber!
4. You probably didn't notice the main reason for using fixed-length string ID! Here will be explanation by example:
You have a ID in string format like in your opening post with un-fixed length. Let's assume this ID is Primary Key, i.e. records in your table are ordered by it. And let's assume the order is from smallest to largest.
You add a 1st record:
"A-1", ...;
You add other records up to 9th:
"A-1", ...
"A-2", ...
...
"A-9", ...
You add 10th record:
"A-1", ...
"A-10", ...
"A-2", ...
...
"A-9", ...
You add 11th record:
"A-1", ...
"A-10", ...
"A-11", ...
"A-2", ...
...
"A-9", ...
You add 101th record:
"A-1", ...
"A-10", ...
"A-101", ...
"A-11", ...
"A-2", ...
...
"A-9", ...
Etc.
And the For cycle in your code to add preceding zeros is moot, as with your current code until "A-99999" you get proper ID's, but after this all will continue as in my example. I.e. You get entries like (and no leading zeros, of-course)
"A-100001", ...
"A-1000010", ...
"A-10000101", ...
"A-1000011", ...
"A-100002", ...
...
"A-100009", ...
And in case you set the For cycle with bigger length, as I adviced, then why to use a cycle, when a simple Right() formula will do?
On based on this (your answer to Ajax) was my post.
1. Why have a long procedure when a single formula will do?
2. "fnum = fnum + 1" You are adding 1 to string! It will not work in Access (but it will work in Excel)!
3. I used CLng() - it converts string to ńumber!
4. You probably didn't notice the main reason for using fixed-length string ID! Here will be explanation by example:
You have a ID in string format like in your opening post with un-fixed length. Let's assume this ID is Primary Key, i.e. records in your table are ordered by it. And let's assume the order is from smallest to largest.
You add a 1st record:
"A-1", ...;
You add other records up to 9th:
"A-1", ...
"A-2", ...
...
"A-9", ...
You add 10th record:
"A-1", ...
"A-10", ...
"A-2", ...
...
"A-9", ...
You add 11th record:
"A-1", ...
"A-10", ...
"A-11", ...
"A-2", ...
...
"A-9", ...
You add 101th record:
"A-1", ...
"A-10", ...
"A-101", ...
"A-11", ...
"A-2", ...
...
"A-9", ...
Etc.
And the For cycle in your code to add preceding zeros is moot, as with your current code until "A-99999" you get proper ID's, but after this all will continue as in my example. I.e. You get entries like (and no leading zeros, of-course)
"A-100001", ...
"A-1000010", ...
"A-10000101", ...
"A-1000011", ...
"A-100002", ...
...
"A-100009", ...
And in case you set the For cycle with bigger length, as I adviced, then why to use a cycle, when a simple Right() formula will do?
Thank You Sir! GOD BLESS YOU!!
@Kundan - no need to repeat the entire post you are responding to. Just any bits that you refer to in your next post. Otherwise makes threads very long to scan through