Access 2003 query, long list of "or" not working

[Country] <> "USA" And [Country] <> "US" Or And [Country] <> "United States" And [Country] <> "United States of America" And [Country] <>  "United States Minor Outlying Isl" And And [Country] <>  "Canada" And [Country] <>  "CA"

add  And [Country] <>  Null later after testing the rest.

Customers.Country

That is the table and field, and I need to see CustomerID and Country in the results.

How do I take your input and make a query with that? Are you saying use SQL view?

whoo hoo! Thanks.

You are welcome.

BTW, take a look at https://www.experts-exchange.com/articles/18366/Subqueries-at-a-glance.html

for more on queries. Some of them are advance but soon or later you will need them all.

Mike

Will bookmark, thanks much.

You might also want to consider:

WHERE NZ([Country], "") NOT IN ("USA", "US", "United States", "United States of America", "United States Minor Outlying Isl", "Canada", "CA")

Yeah, not in list seems much cleaner and easier to maintain.

Yes, it is better solution. With focus on And Or issue, I didn't want to try other things. I do not mind if you get this question opened to regrade or accept the good solution from Dale.

Mike

Dale, would you like me to do that or do you just want me to get you next time?

just get me next time.  I knew you had accepted Mike's answer, just wanted to present you with another option.

One of the down sides of using the IN ( ) syntax is that they seem to take longer to run than the equivalent

<> "A" AND <> "B" AND ...

syntax.  A lot of times.  I'll build a table of words to exclude from a query, then use a left join from the main table to that table, something like:

SELECT T.*
FROM myTable as T
Left Join tbl_ExcludeWorks as E ON T.[Country] = E.Exclude
WHERE E.ID IS NULL

This syntax runs quicker, but may not be updateable depending on you keys and unique indices.  So if you need undateability, you would have to test it first.

Very good info. Thanks much.

@Dale,

I wonder if we can remove NZ() from left side. You have it like:

WHERE NZ([Country],"") NOT IN ("USA", "US", "United States", "United States of America", "United States Minor Outlying Isl", "Canada", "CA")

It will be faster doing like:

WHERE [Country] NOT IN ("USA", "US", "United States", "United States of America", "United States Minor Outlying Isl", "Canada", "CA") AND [Country] Is Not Null

The reason is that use of function on the left side is know as nonsargable query and thus very slow if the searched fields are indexed.

"Generally when we include a Function/operation in a column included in WHERE clause the query became Non-Sargable. ..." for more see: http://www.codeproject.com/Articles/827764/Sargable-query-in-SQL-server