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

Bobby
Bobby used Ask the Experts™
on
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?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mike EghtebasDatabase and Application Developer

Commented:
[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.

Author

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?
Database and Application Developer
Commented:
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...
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
whoo hoo! Thanks.
Mike EghtebasDatabase and Application Developer

Commented:
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

Author

Commented:
Will bookmark, thanks much.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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")

Author

Commented:
Yeah, not in list seems much cleaner and easier to maintain.
Mike EghtebasDatabase and Application Developer

Commented:
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

Author

Commented:
Dale, would you like me to do that or do you just want me to get you next time?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
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.

Author

Commented:
Very good info. Thanks much.
Mike EghtebasDatabase and Application Developer

Commented:
@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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial