I receive a file that has not specifications on the ssn field. I have ran into the problem where some people put dashes and some don't put dashes in the SSN field. Is there a function that takes out a certain character that I would specify in the function like the "-"? Or can anyone think of a way to fix this. I know I can manually import it and go to design view of the table and put 000-00-0000 in the format textbox and that fixes it but I don't want to have to do that everytime I import the file.

First make a copy of the table.

Then develop an update query that uses the Replace function. Develop a new query using the table of interest and the ssn field. Convert it to an Update query (Query--->Update Query)

Update: ssn

Update To: Replace([ssn], "-", "")


I think I have it right but you will want to double-check BEFORE running the query.

Thanks for the reply, I tried the replace function in the Update To section and I received an error message:"Undefined function 'Replace' in expression". Am I missing a library or reference to be able to use this function?

OOPS! My fault, didn't notice the version, Replace does not exist in version 97.

It can still be done, but it will take code to do it.