Restore leading zero

[Xerxov]

I imported an excel spreadsheet with 10 digit ID numbers into Access,
and it dropped all of the leading zeros in the ID number field. I
changed the field type to text instead of numeric, but the zeros are
gone. How can I replace those zeros without having to type them all
in by hand? All of the ID numbers are 10 digits, some have 2 leading
zeros, some 1, and some none.

Or better yet is there simply a way to import the data again without
losing the zeros?

Thanks for your help.

 

[rquintal]

I imported an excel spreadsheet with 10 digit ID numbers into Access,
and it dropped all of the leading zeros in the ID number field. I
changed the field type to text instead of numeric, but the zeros are
gone. How can I replace those zeros without having to type them all
in by hand? All of the ID numbers are 10 digits, some have 2 leading
zeros, some 1, and some none.

Or better yet is there simply a way to import the data again without
losing the zeros?

Thanks for your help.

now that you have a text type field, create an update query that
concatenates 10 zeroes to the left of the number in the field, then
selects the rightmost 10 digits, and updates the table with the new
numeric string.

The update to row of the query would be Right("0000000000" & [number],
10) -- replace [number] with the name of the field.

Practice on a copy of the table first.

 

[Xerxov]

I imported an excel spreadsheet with 10 digit ID numbers into Access,
and it dropped all of the leading zeros in the ID number field. I
changed the field type to text instead of numeric, but the zeros are
gone. How can I replace those zeros without having to type them all
in by hand? All of the ID numbers are 10 digits, some have 2 leading
zeros, some 1, and some none.

Or better yet is there simply a way to import the data again without
losing the zeros?

Thanks for your help.

now that you have a text type field, create an update query that
concatenates 10 zeroes to the left of the number in the field, then
selects the rightmost 10 digits, and updates the table with the new
numeric string.

The update to row of the query would be Right("0000000000" & [number],
10) -- replace [number] with the name of the field.

Practice on a copy of the table first.

Thank you. I'll give that a try.

 

[Xerxov]

now that you have a text type field, create an update query that
concatenates 10 zeroes to the left of the number in the field, then
selects the rightmost 10 digits, and updates the table with the new
numeric string.

The update to row of the query would be Right("0000000000" & [number],
10) -- replace [number] with the name of the field.

Practice on a copy of the table first.

Thank you. I'll give that a try.

It worked. The funny thing is I'd found another post with the same
solution and had tried it, but instead of actually running the query I
just hit the table view option, and it showed no leading zeros so I
assumed it didn't work. The same thing happened with this, but I
realized maybe it wasn't showing the zeros because I had not actually
run the query and they were not in the table yet... Thanks again.