Convert Number

[Brianfree]

Hi i have a field in an access table which is a number ie...

2, 45, 2345, 100987

I need to convert all numbers so they are all 5 chars long ie..

00002, 00045, 02345

Is there a query that i can do this in?

Many thanks,

Brian

 

[lespaul]

SELECT Format(FieldName,"00000") FROM TableName

Leslie

Have you met Hardy Heron?

 

[Brianfree]

Thanks! that works! - can i use this in sn update statment to actually change the data in the table?

Kindest thanks,

Brian

 

[hovercraft]

Brian,

Go into design view of the table, select the field and look at it's properties. Put 00000 in the FORMAT property.
that should do it. -save table.

 

[dhookom]

If your field is numeric, then you can't store leading 0s. You can display leading zeros in controls. If you want to actually store leading zeros, your data type would need to be text. You could run an update query to add the leading zeros.

Duane
Hook'D on Access
MS Access MVP