How do I insert leading characters in a field?

[aceman62]

I want to pad the field with leading zeros so it is 8 characters in length.
If the user enters 123, I want it to default to 00000123.

 

[Tom Ellison]

Dear Man:

What PCD wrote may be a good solution. However, if you want the column to
sort correctly, you would need to change the data. I would use
Right("0000000" & CurrentValue, 8)

Tom Ellison

 

['69 Camaro]

Hi, Jamie.

To finish the job properly:

Make the column fixed width 8 characters i.e. CHAR(8) rather than
VARCHAR(8).

Use a validation rule to disallow non-numeric characters

Dang! You don't much like Access users and developers, do you? ;-)

You would rather the users type in all eight digits, including the leading
zeros? You would rather the developer store eight characters (or 16 if
Unicode compression is avoided), and create a validation rule to ensure that
only digits are stored in the column, instead of storing a four byte Long
that needs no such Validation Rule or leading zeros? All this so that the
developer doesn't have to write a simple formatting function that displays
the digits with leading zeros? (There's no sorting problem with the digits
not lining up properly, so one doesn't have to worry about this column's
sort order not being correct.)

my_col LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

I must have the buggy version of Access because when it's in ANSI mode this
syntax automatically gets rewritten as:

"my_col" ALike '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

.. . . which obviously doesn't work. I have Access 2003 SP-1 installed on
this computer.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.

if you want the column to
sort correctly, you would need to change the data. I would use
Right("0000000" & CurrentValue, 8)

To finish the job properly:

Make the column fixed width 8 characters i.e. CHAR(8) rather than
VARCHAR(8).

Use a validation rule to disallow non-numeric characters (make it
implementation-independent, i.e. handle both 'ANSI mode' and other
mode, by avoiding wildcard characters):

my_col LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'

Jamie.