Adding "is Null" to a query produces unexpected results

[Guest]

I am designing a query in Access2002 that lists the names & adresses for our clients for the past 3 years. We are doing a mailing to US only adresses. When I do a query for all clients I get 2157 records. 170 of those are from other countries. When I add the criteria "is null" to the Country field, (We leave USA blank in the country field) instead of the expected 1987 records I get 1591 entries. I cannot figure out what is happening to the other 396 records. I have tried "" and not is null. Do I have to add USA to all records with a blank country field and then query for USA

Thanks for any help.

 

[John Spencer (MVP)]

Perhaps there is something in the fields like a zero-length string or multiple
spaces. In Access plus JET this is normally not the case, but if you are using a
linked table (Excel, SQL Server, etc) it is possible to have "invisible" characters.

TRY

WHERE Country & "" = ""

That basically adds a zero-length string to anything that is there and then
checks if it is equal to a zero-length string.

If that fails then try adding a Trim statement around the expression

TRIM(Country & "") = ""

 

[Guest]

Thanks so much John, adding the WHERE statement in SQL did the trick!