Making all numbers the same length, by adding zeros

[SteveC24]

Hello all,

I have a table, with a whole series of reference numbers in, which I have moved over from an old database, and am trying to integrate the data into my new database.

The numbers, in order to work with my new db need to be 6 characters long (as they are looked up in a sql-stored table that another application uses), otherwise it won't match up the clients to the reference numbers properly.

Anyway, some of these numbers are 6 digits anyway, such as 123456, but some are just 6543....I need 6543 to be changed to 006543, thereby creating a 6 digit number.

This, I am sure, is pretty simple, and only needs to be done the once - but I can't think of a way of doing it!

Any help much appreciated!

 

[SteveC24]

Oh, I should add that the numbers are stored as text, so having leading zeros won't be a problem.

 

[meanster99]

This is probably a convoluted method but it should work but only to change 4 digit numbers to six digits (but you can do it again for different lengths)

Create a select query that contains the field you want to change. Set the criteria in this field to Like "####" (i.e it will only select those numbers that do not have 6 digits or Not Like "######")

Now you should have a query that only selects the numbers you need to change. You then add a calculated field to this query and put two zeros next to it (i.e NewNo: "00" & [YourField Name] ).

Run the query and copy the entire NewNo column and paste it into the YourField Name column.

The underlying table should then contain the new 6 digit numbers. As I said you can do this again if there are other numbers with different lengths.
As you can see this is a long-winded way if there are varying lengths of digits but it should work!
HTH

 

[Laurentech]

In code you could do Format(MyNumber, "000000")

Larry

 

[SteveC24]

Thank you everyone! I used the Format(MyNumber, "000000") in the end - works a treat.

Thanks!