How to add leading zeros to a field through a query

[accessmatt]

Hello,

I have data in a table where one of the fields is a text field full of numbers in each row, i.e. 56791, 345672. At this point, with the data already in the table, how can I create a new table/query to force a character limit using leading zeros. For instance every entry has to be 10 digits so query would do this: 0000056791 and 0000345672.

Thanks,

[JoeM]

One way is to do this through a Update Query.
Select all records where the length of your field is less than 10.
Then, append 10 zeroes to the entry in your field, and take the 10 right-most characters.

The SQL code of that Query will look something like this:

UPDATE Table1 
SET MyField = Right("0000000000" & [MyField],10)
WHERE Len([MyField])<10;

[CJ_London]

txtnumber=format(myfield,"0000000000")

or if you want to keep it's numeric properties, change the field type to long and simply put 0000000000 in the format property of the table field or form/report control or use the above format function in a query

Not sure how you would force a character limit using preceding zero's, the usual way would be to set the character limit to 10 in your field size property

[accessmatt]

@joeM, thanks this solution worked great!