Re: Larger field than thought?

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




.

Loading