Need to have a field always have 13 characters & insert leading ze

[WolfDog]

I need to create a table that includes a field that has to have 13 characters
(numbers) in it. If the user only enters 9 digits (or 11), it needs to
insert leading zeroes to make the value 13 characters. The value has to
include all 13 characters so it can't just be a display thing. The value has
to actually be 13 characters long.

Thanks!

 

[WolfDog]

Dorian,
What if I am not using forms and entering data directly to table (or
importing data to table?)

 

[Clifford Bass]

Hi,

Try setting the Validation Rule property for the column to:

Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]"

And so as to make it kinder to the user set the Validation Text
property to something like:

Please enter a 13-digit number.

Clifford Bass

 

[Jeff Boyce]

Depends on whether you want to STORE thirteen characters for each value or
DISPLAY thirteen characters.

If you need to store 13, consider using a Text data type for the field.

If you wish to display 13, and plan to "do math" on the values stored, use
numeric AND use a format as advised else-thread.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Duane Hookom]

IMO, don't allow data entry directly into tables. Always use forms for user
interaction.

 

[WolfDog]

Jeff,
I need to actually store the value as 13 characters. The intent is to allow
the user to input their internal "item #" and have it automatically add
leading zeroes and store that value as a 13 character string. The end user
of the value requires a 13 digit number. No math will be done...reference
only.

 

[WolfDog]

The intent is to allow the user to input their internal "item #" and have it
automatically add leading zeroes and store that value as a 13 character
string. The end user of the value requires a 13 digit number. No math will
be done...reference only. The automatic part part is where I am having
trouble.

Hi,

Try setting the Validation Rule property for the column to:

Like "[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]"

And so as to make it kinder to the user set the Validation Text
property to something like:

Please enter a 13-digit number.

Clifford Bass

Dorian,
What if I am not using forms and entering data directly to table (or
importing data to table?)

 

[Clifford Bass]

Hi,

For direct entry or imports, I do not believe you can do the automatic
padding of zeroes. For imports, you could run a process after the fact that
pads those fields that do not have thirteen digits. Or write your own
customized import process.

Be that as it may, my recommendation would be to go with a
numeric/decimal value that allows for up to thirteen digits and has its
format set to "0000000000000". This will work in all cases and will always
show the zeroes. The end-user does not need the thing stored as a text field
with all thirteen characters. Yeah, they may "require" it, but if they
always see of thirteen characters, why should they care about what is
underneath? When stored as a numeric value, searching will be simplified.
No need to type all thirteen digits into the search dialog or other places
that ask for an item number.

Clifford Bass

 

[Larry Daugherty]

Don't know if you found a solution to your issue. The below little
routine returns a 13 character string with leading zeros and the
number set to the right.

HTH