Set character specificiations for field in a table

[Becky N]

Hello,

I am working in a table in MS Access 2003 and one of my fields is store
number. Stores are either a 3- or a 4-digit number (ex. 111, 1234). I have
the data type set as text, but I can change it to number if it needs to be.
Is there a way that I can set the field properties for the store number field
so that the store number will always appear as a 4-digit number and if the
store has only 3-digits it has a leading zero? Basically, if it is 4-digits
already it will appear as is (ex. 1234), but if it is only 3-digits it will
look like this: 0111.

Any help you can provide is appreciated. Thanks!

Becky

 

[ghetto_banjo]

well if you have the data type as text, it will store leading zeros
and not remove them. if you keep it as text, you would want to
actually enter the data as: "0111" and not "111".


if the data type is a number, you would just need to format the field
on reports and such so it appears to have that leading zero,

i.e.

Format([StoreNumber], "0000")

 

[Jerry Whittle]

Leave it as a number field. Put something like below in a query or where
needed in a form or report:

String(4-Len([FieldName]),"0") & [FieldName]

If the field is null, only one 0 will show.