When I created the database we use (3 years ago), I thought that the
largest
number we would hit would be 999. Well, we are at the high 800's now on a
few of the fields we use. What I do is use my own table to keep the
current
count on records. So a new part # would end with 862, and then the next
one
would be 863, etc...
Obviously with the code snippet below, you can see how the earlier stages
of
numbers 1 through 99 would be upgraded to be a 3 digit code to build onto
the first ****tion of the part number.
Do While Len(strTempStr) < 3
strTempStr = "0" & strTempStr
Loop
I have the database create it's own part numbers, and so the first entry
into a category could be like;
B-APP-001
But since the actual count is only a numeric 1 pulled from the counter
file,
I check to make sure the number generated gets filled with preceeding
zeros
so all part numbers have the same lengths.
As I see it, I can change the above routine to check for 4 digits, instead
of the 3 it currently does when new parts are generated. However, I would
like to convert all existing part numbers over to the 4 digits so that all
parts would have a new 10 digit part number instead of the current 9
digit.
So B-APP-001 would turn into B-APP-0001.
Adding new numbers is an easy fix, but changing all existing part numbers
in
the system, can I run a query update that would take all part numbers
(4,000
part numbers) and just have it add the extra ZERO?
IE: Some form of loop to go through all parts in the database
Get next part number in database
New part number Left(6) + "0" + Right(3)
Loop back to get next part
Can anyone give an example of code to put in an update query (if that is
the
place I should do this) so that I can change all my part number from 9
digits to the new proposed 10 digits?
I have only done update queries twice, and that with the help of this
group.
Thank you for any help you can provide.
Tim
|
