• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2422
  • Last Modified:

Not Like IN Access Query not Working

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

?
1
Ernest Grogg
Asked:
Ernest Grogg
1 Solution
 
Dale FyeCommented:
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
0
 
Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
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*")
0
 
John TsioumprisSoftware & Systems EngineerCommented:
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...
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Ernest GroggAuthor 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
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
How about

Not IN ("HWW")
0
 
Ernest GroggAuthor 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
0
 
John TsioumprisSoftware & Systems EngineerCommented:
how about using a smaller subset of your data...just make a copy of your data and keep only lets say 100 records...
0
 
Ernest GroggAuthor Commented:
Created a new table and ran a query on that..nothing..

Is 72 records.
Database5.accdb
0
 
John TsioumprisSoftware & Systems EngineerCommented:
its the null that causes the problem
Not Like "*hww*" Or Is Null

Open in new window

0
 
Ernest GroggAuthor Commented:
Wow....Thanks so much John!
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now