Solved

Not Like IN Access Query not Working

Posted on 2016-10-11
10
491 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 8

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 16

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

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 16

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 16

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Sql case statement to calculate totals 5 37
compare date to getdate() 8 18
add dollar amount in VB code, Access 2003 6 15
Not listening to where 1 24
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

730 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