Re: Larger field than thought?

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


"John W. Vinson/MVP" wrote:

On Fri, 1 Aug 2008 14:28:10 -0700, "Tim" <noemail@xxxxxxxxxxx> wrote:


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

.

Relevant Pages

  • Re: How should I generate a primary key?
    ... the external reality and verify them. ... be verified for syntax or check digits inside itself. ... A surrogate key is system generated to replace the actual key behind ... with a quote from Dr. Codd: "..Database users ...
    (comp.databases)
  • Re: encryption with python
    ... > and a birth date (8 digits, could be padded to be 9) and obtain a new ... quick-and-dirty student ID algorithm: ... I'd check the database to see if the number has ... an attacker might be able to work out roughly what ...
    (comp.lang.python)
  • Default Value of field based on another field of same record
    ... Retirement Default value to be set to last day of the month when age is ... of date Batch field above). ... how can I extract the first four digits from the field 'OfficerID' ... I can post the database if required. ...
    (microsoft.public.access.gettingstarted)
  • Re: losing precision while selecting NUMBER values from Oracle 7.3
    ... > DBI script selects/displays 39 digits after the decimal point, ... > digits from target, which is a Oracle 8 database. ...
    (perl.dbi.users)
  • Re: Parts Numbering Scheme
    ... >numbering scheme. ... The next two digits indicate a major subsystem or group of related parts ... A big reason why local phone numbers in the US are ... I would create a database to go with this, with the part number as the ...
    (sci.electronics.design)