Adding preceding zeros to 5 character field

[SusanS]

The original field has 1, 2 or three characters, i.e
1, 12, or 1234. I need to put preceding zeros so it wil
always fill up five spaces, i.e.
00001 or
00012 or
00123

Right now the original field is a whole number field. Do I
need to change it to text. What is the right syntax to
put into an update query. I don't know sql.

 

[Ken Snell]

UPDATE TableName
SET FieldName =
Right("00000" & FieldName, 5);

 

[Wayne Morgan]

A number field won't store the preceding zeros. There are two options, you
can change the field to text or you can simply format the data to show the
leading zeros when displayed.

If you choose to change the field to a text field, then the update query to
add in the leading zeros would be something like this:

UPDATE Table1 SET Table1.Field1 = Format([Field1],"00000");