Re: Larger field than thought?

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: Paper & pencil password algorithm
    ... currently I am using a 10x10 Polybius square. ... How would you convert base 11 digits to base 10 digits with uniform ... characters to find on the keyboard. ... I'm undecided on whether it should be 2 or 3 foldsums. ...
    (sci.crypt)
  • Re: Question for the math wizards...
    ... string of characters that isn't too long, ... bits per character with base-32 encoding, then we are limited to shipping ... to know if it was possible given m to map m via a function Fto an m' ... In real world terms, say n is 100 digits, m is 50 digits, and I want to ...
    (sci.crypt)
  • Re: Invariant with DIGIT-CHAR-P and the reader.
    ... should appear to the Lisp reader as a number. ... >> reading of the standard makes me believe the Lisp reader should ... > Why a new CL could not accept more forms for unicode digits? ... a..z, characters chosen by the implementation, and not from some higher ...
    (comp.lang.lisp)
  • Re: Calling Bob Phillips - Please
    ... The cell can hold test strings of up to 32k characters. ... cell display. ... Therefore the maximum number of digits ... This works from row 1 to the last row and concatenates values from ...
    (microsoft.public.excel.programming)
Loading