Pad numbers with leading zeros in an Access query

Question

I have a column of numbers between 0 - 6 digits long. For those less than 6 I need to pad out with zeros to ensure they are all 6 digits i.e 12563 = 012563 or 23 000023 etc etc. Can someone recommend a solution?

Answer 1

Probably the easiest way to pad numbers with leading zeros would be to use the Format() function, as in

Format(fieldName, "000000")

Answer 2

If you're searching on this (like for PIN numbers, where '12' would be represented as '000012' here's an example using Gord's correct answer;

SELECT CStr(Format(fieldName,"000000")) FROM table WHERE CStr(Format(fieldName,"000000"))="000012";