Imported data doesn't show up in form using query for data

[ITguy1981]

Right now I have a table that has "FirstName", "MidName", "LastName", "SSN", and "DOB". To help make searching easier to find data I have a form based off a query that pulls all of the above data, but the "SSN" criteria is base on Like [Forms]![FormName]![SSN]. This way the user can open a search form, enter the SSN, click the search button on the form, the search button opens the form pulls data from the query which would be only the record that had the SSN entered. It works perfectly except for data that has been imported to the table. I imported data from an Excel sheet in which the columns and data matched and I received no import errors. When search for one of the SSN from the table I get no results found. If I run the query that looks for the SSN entered in the form it asks for a SSN, I paste it in, and it works. If I open my main form that has all of the record data it ask for a SSN because it's based on the query. I can paste the SSN and it works. For some reason if I open my search form and enter the SSN it doesn't work. Here's the kicker. If I enter a new record within the database my search works fine. It only doesn't work with records that were imported. Any ideas. I can provide my database if needed without records due to social security numbers. However I have a feeling if you make an excel sheet with those 5 fields and made a couple records there and imported you would get the same result.

 

[John Big Booty]

I'm wondering if the imported data has some non printing characters prefixing it. What happens if you use the following criteria;

Like "*" & [Forms]![FormName]![SSN]

 

[ITguy1981]

Actually, here is the criteria from the query, copied and pasted.
Like "*" & [Forms]![SSN Search]![SSN] & "*"

What I don't understand is I just run the query, I get a box that opens saying enter parameter value Forms!SSN Search!SSN, and I can enter a SSN and the query finds the person no problem. I believe the problem lies somewhere within the entry of the SSN number going in to the form and then going to the query because manually putting the data in the query works.

 

[ITguy1981]

Well I believe I've found the issue. Because I'm importing from Excel which has the social security numbers with the dashes in them the mask does me no good. The mask has the dashes in between the numbers, but the query is not reading those as characters that match. If I actually enter the number manually as 555-55-5555 and put the dashes in myself it works. Would changing the type of field from text to number fix this issue? Should I just delete the dashes from the excel file and then import the data?

 

[John Big Booty]

Yes, I'd do the import in two steps. First import your data into a temporary table, then use the Replace() function to strip out the dashes,

Replace([YourSSNFieldName],"-","")

in an Update Query would strip out your dashes, then move the cleansed data into your table.