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

I'm trying to query a table to not give me results for the following:

Not Like "USA" Or "US" Or "United States" Or "Untied States" Or "United States of America" Or "United States Minor Outlying Isl" Or "Canada" Or "CA" Or Is Null

I do not want the results from all the quotated ones, and I do want the null results. This returns all results and looks like it doesn't even factor in my conditions. What am I doing wrong?
LVL 1
BobbyAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
[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.
BobbyAuthor Commented:
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?
Mike EghtebasDatabase and Application DeveloperCommented:
First try:
Select CustomerID, Country
From Customers
Where [Country] <> "USA" 

Open in new window


Then add more:
Select CustomerID, Country
From Customers
Where [Country] <> "USA" 
And [Country] <> "US"

Open in new window


And so fort.  BTW I had an extra Or in:
[Country] <> "USA" And [Country] <> "US" Or And [Country] <> "Uni...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

BobbyAuthor Commented:
whoo hoo! Thanks.
Mike EghtebasDatabase and Application DeveloperCommented:
You are welcome.

BTW, take a look at http://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
BobbyAuthor Commented:
Will bookmark, thanks much.
Dale FyeOwner, Developing Solutions LLCCommented:
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")
BobbyAuthor Commented:
Yeah, not in list seems much cleaner and easier to maintain.
Mike EghtebasDatabase and Application DeveloperCommented:
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
BobbyAuthor Commented:
Dale, would you like me to do that or do you just want me to get you next time?
Dale FyeOwner, Developing Solutions LLCCommented:
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.
BobbyAuthor Commented:
Very good info. Thanks much.
Mike EghtebasDatabase and Application DeveloperCommented:
@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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.