Solved

Not Like IN Access Query not Working

Posted on 2016-10-11
10
62 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 5

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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

757 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

21 Experts available now in Live!

Get 1:1 Help Now