Larger field than thought?

[Tim]

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 portion 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

 

[Steve]

My suggestion to make everything easier and you won't ever have to worry
about this problem again is to store part # number part as digits only with
no leading zeros. When you wanted to display a part # in a form or report,
you would just need to format it there to show leading zeros. If the design
of your tables is correct, you only need to update the number part of the
part# to just digits.

Steve

 

[John W. Vinson/MVP]

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?

Steve's suggestion is a good one. You have what's called an
"Intelligent Key" - and unfortunately that's not a compliment! Storing
multiple different pieces of information in one field as you are doing
is a bad idea, for the very reason you have now encountered.

Fortunately you can either split the field into two or three fields
(as Steve suggests) and concatenate them for display; or if you
prefer, just change the Text field size from 9 to 10 (these *are not
numbers!!!!*) and run an Update query updating the field to

Left([PartNumber], 6) & "0" & Right([PartNumber], 3)

This will split the field into pieces "B-APP-" - the first six
characters; append a literal 0 character; and then put on the last
three characters.

Back up your database first of course!!!!!

 

[Jacqueline]

John,
This is unrelated to this post, but after reading it I thought you might be
able to help me. I need to generate automated account numbers for a new
database. I am thinking of using Autonumber and seting at 7 diget number to
start the accounts.

It was suggested that I create code to look at the last number used in the
field and add one. But I an not that up on VB and I thought that you could
not have calculated fields in a table. Any suggestions??
Thanks
--
Jacqueline Staley

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?

Steve's suggestion is a good one. You have what's called an
"Intelligent Key" - and unfortunately that's not a compliment! Storing
multiple different pieces of information in one field as you are doing
is a bad idea, for the very reason you have now encountered.

Fortunately you can either split the field into two or three fields
(as Steve suggests) and concatenate them for display; or if you
prefer, just change the Text field size from 9 to 10 (these *are not
numbers!!!!*) and run an Update query updating the field to

Left([PartNumber], 6) & "0" & Right([PartNumber], 3)

This will split the field into pieces "B-APP-" - the first six
characters; append a literal 0 character; and then put on the last
three characters.

Back up your database first of course!!!!!

 

[John W. Vinson/MVP]

John,
This is unrelated to this post, but after reading it I thought you might be
able to help me. I need to generate automated account numbers for a new
database. I am thinking of using Autonumber and seting at 7 diget number to
start the accounts.

It was suggested that I create code to look at the last number used in the
field and add one. But I an not that up on VB and I thought that you could
not have calculated fields in a table. Any suggestions??
Thanks

An Autonumber is generally NOT a good idea for this purpose.
Autonumbers have one purpose and one purpose only: to provide a
guaranteed unique meaningless identifier. They will always have gaps
(even if you start to enter a record and then cancel the entry by
hitting the Esc key twice, you'll "use up" an autonumber value and
leave a gap); running an Append query can leave a huge gap; they can
even become random if you Replicate your database.

Instead, you would calculate a value - *in a form, not the table* -
and store it in the table.

One way (which would be OK for a single user system or one where only
one person is likely to create new accounts) is pretty easy. Let's say
your table is named Accounts and your number primary key is AccountNo.
Use a form to enter data (you'll need to ensure that ONLY the form is
used, no table datasheets), with a textbox named txtAccountNo bound to
the AccountNo field, and put the following code in the Form's
BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtAccountNo = NZ(DMax("[AccountNo]", "[Accounts]")) + 1
End Sub

If you search the newsgroup for "Custom Counter" you'll find more
robust examples; this one runs the risk that two users might be
creating account numbers at the same moment and both get the same one.

 

[Tim]

So B-APP-001 would turn into B-APP-0001.

Steve's suggestion is a good one. You have what's called an
"Intelligent Key" - and unfortunately that's not a compliment! Storing
multiple different pieces of information in one field as you are doing
is a bad idea, for the very reason you have now encountered.

That is a bummer....I thought it was a good idea.

When I enter a new part number, I enter the primary, and
then the secondary fields; then let the database grab the
next number from the counter as it creates the record.
Format it to 3 digits, and bam!....I have a new part number.

I can see where having the fields concatenated when using
reports or display, but I created the database this way
(9 digit required part number) so that the entering of
data is easily done. At least to my thinking.

I enter hundreds of parts from log sheets each day and
knowing that all parts are 9 digits 'seems' to make it
easy. Maybe I did this because in the old days (mainframe)
databases, this is what I used and actually liked the format.

Somehow, I know that T-PAI-015 is a 7" roller. I don't know
if I could change to a T-PAI-15. Oh, I probably could, but
at this stage of the game, do I want to?

Fortunately you can either split the field into two or three fields
(as Steve suggests) and concatenate them for display; or if you
prefer, just change the Text field size from 9 to 10 (these *are not
numbers!!!!*) and run an Update query updating the field to

Left([PartNumber], 6) & "0" & Right([PartNumber], 3)

I thought that was the Update Query I would be looking at.

I just went back to look. When I created the database, I
built it with a tblBuildMajor and a tblBuildMinor. These
two tables hold the categories available. The fields chosen
from these tables do end up in the tblItem table for each
part created, but I do not actually hold the number of the
part. IE: number 1, or 15, or 294

Instead, another field in the tlbItem actually holds the
new part number; T-PAI-015. The T and the PAI are
fields in the item table, but the only place you will see
the number of the part is in the field itemPartNumber where
the 015 would show as well as the T-PAI to give you
a T-PAI-015 display.

So I can change the database field for the item number
to be 10 digits and add an extra zero to insure all parts
are the same length;, or I would have to extract the 3
digits to the right of the part number and create a number
out of it. Then populate the record with this number
since there is no field currently to hold it.

Yes, I maybe should have reviewed that area from the
beginning, but I thought this would not be a problem,
yet it has become a minor one now.

Back up your database first of course!!!!!

Most definately! I wouldn't attempt this without doing
that. The plan is to update the database next week
when I am the only one with access to it. There are
some other tweaks I want to make and the only other
person who uses this database with me, is on vacation.

Thank you, and all who responded, for the helpful
suggestions and pointing out where I may have done
a better job in the planning, so this issue would not
have arisen.

Tim

 

[Jacqueline]

John,
This is exactly what I was looking for. Thank you so much
--
Jacqueline

John,
This is unrelated to this post, but after reading it I thought you might be
able to help me. I need to generate automated account numbers for a new
database. I am thinking of using Autonumber and seting at 7 diget number to
start the accounts.

It was suggested that I create code to look at the last number used in the
field and add one. But I an not that up on VB and I thought that you could
not have calculated fields in a table. Any suggestions??
Thanks

An Autonumber is generally NOT a good idea for this purpose.
Autonumbers have one purpose and one purpose only: to provide a
guaranteed unique meaningless identifier. They will always have gaps
(even if you start to enter a record and then cancel the entry by
hitting the Esc key twice, you'll "use up" an autonumber value and
leave a gap); running an Append query can leave a huge gap; they can
even become random if you Replicate your database.

Instead, you would calculate a value - *in a form, not the table* -
and store it in the table.

One way (which would be OK for a single user system or one where only
one person is likely to create new accounts) is pretty easy. Let's say
your table is named Accounts and your number primary key is AccountNo.
Use a form to enter data (you'll need to ensure that ONLY the form is
used, no table datasheets), with a textbox named txtAccountNo bound to
the AccountNo field, and put the following code in the Form's
BeforeInsert event:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtAccountNo = NZ(DMax("[AccountNo]", "[Accounts]")) + 1
End Sub

If you search the newsgroup for "Custom Counter" you'll find more
robust examples; this one runs the risk that two users might be
creating account numbers at the same moment and both get the same one.