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


B

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.
 
Ad

Advertisements

D

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
 
P

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")
 
B

Bill

Thanks Doug,
Would I do that in a query?

Douglas J. Steele said:
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
 
Ad

Advertisements

D

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")
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top