Zero Filling Autonumber Fields

[Steve]

I have a database where I want to use an autonumber field to make SKUs
for a new product when it is entered. Is there a way I can make the
field zero fill to a certain length? For instance, if I want the field
to be five digits in length, and I enter 123, I want the stored value to
be 00123. Is there a way I can do this? Or is there a better alternative?

Thanks.

Steve

[Keith Wilby]

Steve <ra***********@hotmail.com> wrote:

if I want the field
to be five digits in length, and I enter 123, I want the stored value
to be 00123. Is there a way I can do this? Or is there a better
alternative?

Hi Steve.

An AutoNumber field should be used only for system purposes, ie for the
generation of a PK for indexing and joining to another table. Most
developers will hide the PK/FK fields from users since they have no meaning
to them. Also, AutoNumber discards 'dirty' numbers, whether or not you
save the record, and so is not necessarily sequential.

I think you need to devise a custom sequential auto-numbering system, which
will require a little VBA code.

Regards,
Keith.
www.keithwilby.com

[Salad]

Steve wrote:

I have a database where I want to use an autonumber field to make SKUs
for a new product when it is entered. Is there a way I can make the
field zero fill to a certain length? For instance, if I want the field
to be five digits in length, and I enter 123, I want the stored value to
be 00123. Is there a way I can do this? Or is there a better alternative?

Thanks.

Steve

? format(123,"00000")
00123

If you want to zero fill and store the value in a table then make sure
the field is Text.

Here's another method.
? right("0000000000" & 123,5)
In this case, I string a bunch of zeros in front, concatenate the zeros
to 123 and end up with 00123 by extracting the last 5 characters.