Solved

Not Like IN Access Query not Working

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

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
Master Your Team's Linux and Cloud Stack!

The average business loses $13.5M per year to ineffective training (per 1,000 employees). Keep ahead of the competition and combine in-person quality with online cost and flexibility by training with Linux Academy.

 

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
 

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

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

786 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