Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3022
  • 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
Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

 
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 Access MVP)Database ArchitectCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

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