Solved Importing tables into new database and automatically formatting...

[SimonSezz]

I'm working on a new Access program and I have recieved a bunch of tables from the programmer that worked on the previous version of the program. Some of the tables contain the Social Security numbers in a column and the SSN is formatted in a different way than the way it is in my program. His format has a "0" in front of the SSN. So if the SSN is "333-45-6789" he has it in the table as "0333456789". In my tables it is formatted as "333-45-6789". Now I could delete the zero manually from each SSN in his table and then just copy everything into my table but it would take forever considering there are over 4,000 rows in the table. Is there any simple and quick way to do this?

 

[David Eagar]

A thought, download to Excel, search & replace, upload???

 

[SimonSezz]

I tried that but then it replaces all zeros in the SSN, even the ones that shouldn't be deleted (those that are part of the actual SSN).

 

[David Eagar]

If you format the column as text??

 

[wazz]

with a copy of the table, create a query with your ssn column and a new column that has:

New SSN: Format(Mid([SSN],2),"000-00-0000")

view the results to compare. you might be able to copy the new column into a new column in your table, or just update the current column. check back for ideas as to how to best save the formatting results.

 

[SimonSezz]

Thank you wazz, your solution worked perfectly!