Replace dashes stored in text field

  • Thread starter Thread starter Don
  • Start date Start date
D

Don

I want to write a Access SQL statement that when using a field in my where
clause does not look at certain characters stored in a field. I tried to
user the replace function but it doen't appear in the correct format

Example of data stored in field SSN 111-22-3333.
User type into text box 1112

SELECT tblUser.ID, tblUser.Lastname FROM tblUser WHERE
replace(tblNotesData.InvestorSSN,"-","") like '*1112*'

Thanks

Don
 
Don,
Your code works for me.
Are you sure your version of Access has the replace() function?
Access 2000 or newer.

Or were you trying to use a prompt for the user to enter the #.
If so, use:
WHERE replace(tblNotesData.InvestorSSN,"-","") like "*" & [Enter SSN] & "*";

If it's always the first few digits that are going to be input, then use:
like [Enter SSN] & "*";
 
Back
Top