Not Like IN Access Query not Working

Ernest Grogg
Ernest Grogg used Ask the Experts™
on
hello,

I have a query that I need to filter on one field:

I have used every combination I can think of to get it to work.

(Not Like "*HWW*")
Not Like "HWW"
<>"HWW"


I have about 16,000 records and I can't get this to sort it out.

Like "HWW" works fine, but not using  NOT LIKE

?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Commented:
Well,

Like "HWW"

is the same as:

= "HWW"

Like "*HWW*"

will return all records that contain the string "HWW" anywhere within the field

So, what is it you really want to do?  Write it out in text, don't worry about SQL format
I am a bit confused. Like is an operator to be used with partial matches. If you omit the * there are no partial matches and you are looking for exact matches (or the opposite of exact matches as it may be)

Like is the expression you want to have evaluated, so I imagine it should look like
For exact non matches
<>"HWW"
For not "contains"
Not (Like "*HWW*")
John TsioumprisSoftware & Systems Engineer

Commented:
Maybe your query has OR somewhere that "disables" your NOT Like "*HWW*"
Just take your data and use only the NOT Like Criteria...nothing Else...
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

Ernest GroggSecurity Management InfoSec

Author

Commented:
I am trying to get

All records without the Word:  HWW for a particular field

No other criteria is in the query

searching/Filtering for "HWW" on that field works fine

but not when I don't want "HWW" to be included in the query
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems Analyst
Top Expert 2007

Commented:
How about

Not IN ("HWW")
Ernest GroggSecurity Management InfoSec

Author

Commented:
Nothing on the

Not IN ("HWW")


I removed all except about 5 with the

HWW

and it should return all the records except those 5  but I get nothing returned
John TsioumprisSoftware & Systems Engineer

Commented:
how about using a smaller subset of your data...just make a copy of your data and keep only lets say 100 records...
Ernest GroggSecurity Management InfoSec

Author

Commented:
Created a new table and ran a query on that..nothing..

Is 72 records.
Database5.accdb
Software & Systems Engineer
Commented:
its the null that causes the problem
Not Like "*hww*" Or Is Null

Open in new window

Ernest GroggSecurity Management InfoSec

Author

Commented:
Wow....Thanks so much John!

Commented:
You are a star John,

Today I come across with the same issue what Ernest faced couple of years ago.

Thank you so much. Still I did not get the logic behind it!!!!!!??

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