Query (1 Viewer)

[capogna1]

Hi everyone,
I have a field with social security numbers, data was imported so some came in with dashes some without. Tried to change format to the field to get rid of dashes but didn't work. Now when I search by SSN I have to try with dashes and without. How do I tell Access bring be back either SSN with dashes and without?
Thanks in advance for any response.
Robert

 

[arnelgp]

how do you search, is there a textbox for search

add asterisk in front and in back of the search string:

*searchString*

If you're going to create a query that filter with or without dash:

select * from table where replace([fieldToFilter],"-","") = "filterString"

 

[jdraw]

Are you certain you should be using SSN in your application? Could be a privacy issue.

You could remove the dashes in the SSNs with dashes. See the Replace function.

 

[theDBguy]

Hi Robert. Welcome to AWF! You might also consider cleaning up the data so they are consistent. You should be able to do it quickly using an UPDATE query.

 

[capogna1]

Thanks everyone problem solved!
I was able to remove dashes from the raw data.

 

[theDBguy]

Thanks everyone problem solved!
I was able to remove dashes from the raw data.

Congratulations! Glad to hear you got it sorted out. Good luck with your project.