Automatic Preceeding Zeros in a field e.g. 00001234

[DD]

I have a serial number field that will consist of 8
numbers. Right now, the young serials only consist of 3
numbers but we would like to have the young numbers which
are not yet 8 characters be preceeded by zeros without the
user having to input the zeros. For example serial
00001234 - I would like the user only to be required to
enter 1234 and have the database convert this to 00001234.
Thanks in advance!

 

[Steve Schapel]

DD,

Actually, it is not necessary to "convert" the number. If it is in fact
a number, it will be stored as a number, and you can't put leading
zeros. But this is the actual data in the tables as such, and since you
don't normally look at the tables anyway, it doesn't really matter.
Instead, you can leave the data as a number, but just use the Format
property of the applicable textboxes on your forms and reports to
display with leading zeros. Set the Format property to...
00000000

 

[Jay Vinton]

are not yet 8 characters be preceeded by zeros without the

user having to input the zeros.

If the data needs to be a number, you must deal with the formatting in code at runtime. If your objective is to always have 8 characters in the table, then it should be type String.

Assuming the second case, make the serial number type Text and massage it in code before saving.

This is a good time to stop and think. Will today's serial number format be workable in the future? What if you want to change "00000123" to "ABC-0123" or "ABC-XY-1234567890-P"? If you plan ahead, you'll save a lot of grief when things change.

To solve your immediate problem:

Dim str as String

str = CStr(123)

Do While (Len(str) < 8)

str = "0" & str

Loop

' str = "00000123"

Jay