How to Generate Serial from base 000001 to 999999 or LEFT padding?

How to Implement LEFT Padding in Code or Query.

FROM        TO  
1           000001
2           000002 
10          000010   
110         000110 
1110        001110 
99999       099999 

I am using MS Access开发者_StackOverflow中文版 2007.

Thanks in Regards..

If you want to format a number as a string with leading zeros, you can use the d6 format:

int i = 200;
Console.Write(i.ToString("d6")); // prints 000200

Example: http://ideone.com/fScd9

in VBA, use the Format$ function (drop the dollar sign if you are using variants), and use "000000" for the format string.

format$(serial, "000000")
or
format(serial, "000000")

This will format the string to six digits using zeros where there are no leading numbers.

You can also try right("000000" & serial,6). Using Format is more elegant, however if you are running this on really large datasets or ODBC linked datasets it can be quite a bit slower.

SELECT [serial], right("000000" & [serial],6) AS [PaddedSerial]
FROM Table1