Full Version: Filtering a Field
UtterAccess Discussion Forums > Microsoft® Access > Access Queries
khaynes
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.
fkegley
First make a copy of the table.
When 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.
khaynes
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?
fkegley
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.