Jan 31 2006, 10:30 AM
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.
Jan 31 2006, 10:37 AM
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 To: Replace([ssn], "-", "")
I think I have it right but you will want to double-check BEFORE running the query.
Jan 31 2006, 10:43 AM
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?
Jan 31 2006, 11:41 AM
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.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here