Solved

Not Like IN Access Query not Working

Posted on 2016-10-11
10
125 Views
1 Endorsement
Last Modified: 2016-10-11
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
Comment
Question by:Ernest Grogg
10 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 41839142
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
 
LVL 6

Expert Comment

by:Anders Ebro (Microsoft MVP)
ID: 41839146
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
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41839147
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
 

Author Comment

by:Ernest Grogg
ID: 41839160
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
 
LVL 75
ID: 41839169
How about

Not IN ("HWW")
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Ernest Grogg
ID: 41839180
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
 
LVL 13

Expert Comment

by:John Tsioumpris
ID: 41839181
how about using a smaller subset of your data...just make a copy of your data and keep only lets say 100 records...
0
 

Author Comment

by:Ernest Grogg
ID: 41839186
Created a new table and ran a query on that..nothing..

Is 72 records.
Database5.accdb
0
 
LVL 13

Accepted Solution

by:
John Tsioumpris earned 500 total points
ID: 41839201
its the null that causes the problem
Not Like "*hww*" Or Is Null

Open in new window

0
 

Author Comment

by:Ernest Grogg
ID: 41839210
Wow....Thanks so much John!
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

27 Experts available now in Live!

Get 1:1 Help Now