Help with add leading zero to value

[Cam]

Hello,

I have two tables with a text field and the value in the field range from 50
to 9999.
50 is 2 characters, 100 is 3 characters and 9999 is 4 characters. I ran into
a problem when referencing these values in calculation.

How or what can I do to put two leading zero to 2 character field and one
zero to 3 character field on all existing records? (Maybe a macro, I don't
know) There are currently over 2000 records so it is sufficent to manually
change them all. Any help is appreciated.

Example:
50 = 0050
200 = 0200
950 = 0950

 

[Steve Schapel]

Cam,

Make an Update Query, to update [YourField] to:
Right("00" & [YourField],4)

 

[ND Pard]

Run the following update query, it will change all fields to a four character
field with leading zeroes where appropriate.

UPDATE <your table name> SET <your table name>.<your field name> =
Right("0000" & [<your field name>],4);

Here's an example:

UPDATE Mstr_Acct_Codes SET Mstr_Acct_Codes.Acct = Right("0000" & [Acct],4);

Good Luck.