Not Like in Query still pulling the specific not like values

Lori F
Lori F used Ask the Experts™
I had this question after viewing Not Like IN Access Query not Working.

I have not like values in a query that keeps returning those specific values.  I do not have any "null" values included, nor "or" and even tried to put parenthesis around the value such as Not Like ("Phase").  It's still not working.  Any suggestions are welcome!
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Are you to trying to exclude exact values like 'Phase' or are you trying to exclude partial matches?

If it's the former then there's no need to use Like, you would use <>.


Thank you, Norie!  That didn't work.  Actually my Phase is by location, so I'm trying to exclude the actual location.  Example, I want everything to pull information with the Philadelphia and Denver text, but not the Atlanta text.  There are too many that I want and just a few that I don't need, hence why I went with Not Like instead of Like.
Most Valuable Expert 2012
Distinguished Expert 2018

Welcome to the site!

Please post sample data and expected results.

You also didn't provide what database you are using.  The link posted is Access.  Can we assume Access?
Introduction to R

R is considered the predominant language for data scientist and statisticians. Learn how to use R for your own data science projects.

Most Valuable Expert 2015
Distinguished Expert 2018

I wonder what you expect us to do. We don't have your table, nor your query, not even your criteria. Neither test data, nor expected output.
There is nothing to suggest.
John TsioumprisSoftware & Systems Engineer

It all depends on your query design... probably you have look up fields and you "looking" at the lookup values but not the actual values
Mark EdwardsChief Technology Officer

All we can give you is general ideas for just a general problem with no specifics.  My first response to "My thing doesn't work" is "well....fix your thing".

That said, here's a few general things to look for:
Using "Not" is tricky. Your query first looks at everything that is Like, and then gives you everything that isn't like anything that is Like.
Make sure you have parentheses around just the part of your criteria that is what you want to apply the "Not" to - including "Not" (makes it clearer - no technical reason).
Make sure you use your wildcards correctly:  
Like "*xxx*" gives everything that CONTAINS "xxx".
Like "xxx*" gives everything that BEGINS with "xxx"
Like "*xxx" gives everything that ENDS with "xxx"
Like "xxx" gives the same thing as = "xxx"
Putting "Not" in front of these gives you everything that DOESN'T match what you get with the pure "Like" without the "Not"

For example, if you want to include everything that contains "Atlanta" in it, you would use:
WHERE [Phase] Like "*Atlanta*"
But if you want to exclude everything with "Atlanta" in it, but get everything else:
WHERE (Not ([Phase] Like "*Atlanta*"))

Notice how the parentheses makes SURE we aren't accidently applying "Not" to anything else - just to be clear.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

WHERE ([Phase] Like "*Philadelphia*" OR [Phase] Like "*Denver*") AND NOT ([Phase] Like "*Atlanta*")

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