Adding a prefix number

[CAM]

Hello,

In my query I have a text field called "Location" in this field I usually
have a single digit number or a double or triple digit number. What I want
to do is if a single digit number appears I want the field to be in four
digit: Example if a single digit number "1" appears I want "000" added to
the "1" giving "0001" or if I have a two digit number "14" I want the
field to be "0014" What I did was using a IIF statement such as
IIF([Location]="1", then "0001","") now I know there has to be a better
way. Any tips will be appreciated. Thank you in advance.

 

[Rob Parker]

Location4: Right("0000" & [Location],4)

HTH,

Rob

 

[John W. Vinson]

Hello,

In my query I have a text field called "Location" in this field I usually
have a single digit number or a double or triple digit number. What I want
to do is if a single digit number appears I want the field to be in four
digit: Example if a single digit number "1" appears I want "000" added to
the "1" giving "0001" or if I have a two digit number "14" I want the
field to be "0014" What I did was using a IIF statement such as
IIF([Location]="1", then "0001","") now I know there has to be a better
way. Any tips will be appreciated. Thank you in advance.

Try:

ShowNum:

Right("0000" & [Location], 4)

If you want to store the leading zeroes in the table without typing
them, you can use code in the data entry Form textbox's AfterUpdate
event setting the control using the same expression.

John W. Vinson [MVP]