Find and replace

[Jacques]

Is it possible to make a query that will remove the dashes from a SSN in a
field. I have a DB that was imported from another type of system, and the
import stored the dashes with the SSN's. I need to be able to compare the
old with the new, but the dashes are stopping any records from matching the
SSN's together.

Thanks beforehand,

Oh, and I tried find and replace which didn't work.
I would have also exported the records to Excel, but its way too many for
Excel handle.

 

[Rick B]

There is probably an easier way, but you could update the table with...


NewSS: Left([SSN],3) & Mid([SSN],5,1) & Right([SSN],4)

 

[John Spencer]

You could use the format function to force the existing field to have the dashes
and then use that for the comparison.

Format(SSN,"@@@-@@-@@@@")

You could build a query on the existing table with all the fields and this
formatted SSN. Then use this query in place of the table when you are doing the
comparisons.

I could also suggest other ways to handle this, but you didn't say how you are
doing the comparisons.

 

[Jacques]

Rick,
I appreciate the help, both your and John's ways work well. I'm
actually glad that both of you replied because it helps in other ways
concerning the cleanup job I have to perform (on several DB's having over
220k records each).

Jacques

There is probably an easier way, but you could update the table with...


NewSS: Left([SSN],3) & Mid([SSN],5,1) & Right([SSN],4)



--
Rick B

Is it possible to make a query that will remove the dashes from a SSN in
a field. I have a DB that was imported from another type of system, and
the import stored the dashes with the SSN's. I need to be able to
compare the old with the new, but the dashes are stopping any records
from matching the SSN's together.

Thanks beforehand,

Oh, and I tried find and replace which didn't work.
I would have also exported the records to Excel, but its way too many for
Excel handle.

 

[Jacques]

John,
I appreciate the help, both your and Rick's ways work well. I'm
actually glad that both of you replied because it helps in other ways
concerning the cleanup job I have to perform (on several DB's having over
220k records each).

Jacques