Adding in leading zeros and a space to a text field in a table

[Bill]

I have a new table with an ID field that is formatted as follows: 000123
45678. The ID Field is a Text Field. The leading zeros will vary as some
numbers may have only 1 or 2 zeros. I have an old table that is formatted
1234567. I need to update the old table to the new format so I can merge and
or delete some of the records. I need to add zeros to increase the number of
digits to 10 and add a space in the middle. Any help will be appreciated.

 

[Douglas J. Steele]

Use the Format function on your old ID field (not the Format property of the
field).

Something like

SELECT Format(ID, "00000 00000"), Field1, Field2
FROM OldTable

 

[pietlinden]

I have a new table with an ID field that is formatted as follows: 000123
45678. The ID Field is a Text Field. The leading zeros will vary as some
numbers may have only 1 or 2 zeros. I have an old table that is formatted
1234567. I need to update the old table to the new format so I can merge and
or delete some of the records.  I need to add zeros to increase the number of
digits to 10 and add a space in the middle. Any help will be appreciated.

Format([SomeNumber],"00000-00000")

 

[Bill]

Thanks Doug,
Would I do that in a query?

Use the Format function on your old ID field (not the Format property of the
field).

Something like

SELECT Format(ID, "00000 00000"), Field1, Field2
FROM OldTable

 

[Douglas J. Steele]

Yup. That's the SQL for a query I've shown.

If you've not comfortable working directly with SQL, build the query like
you would normally. In the query grid, where you've got ID, replace it with
Format(ID, "00000 00000")