NOT LIKE query in Access 2013 not working

kyleno

Board Regular
Joined
Jun 9, 2014
Messages
60
I work in MS Access almost exclusively in Design View because I have to do quick little queries often and it is fast and easy. With that caveat, I would prefer to have a solution in Design View if at all possible.

I am trying to combine wildcard queries that are both "Like" and "Not Like". When I attempt to combine them the "Not Like" criteria seems to cancel everything out and the returned results are the entire table that I am querying.

Example 1:
like "ups*" or like "* ups*" or like "*ups*darm*" or not like "*upstate*" or not like "*groups*" or not like "*upstream*" or not like "*upseed*"

I have attempted to trim this to:
like "ups*" not like "*upstate*"

It doesn't seem to matter.

I'm super frustrated. This seems like it would be a very simple function to handle.

Please and thank you.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
I recommend you put parentheses around your criteria with OR so as to keep the logic straight (this can be a cause of easy errors if you start to combine OR with AND later on). Also you need the field name when you use any criteria on a field.

Code:
... where 
(
	(SomeField like "ups*")
	or 
	(SomeField like "* ups*")
	or 
	(SomeField like "*ups*darm*")
	or 
	(SomeField not like "*upstate*") 
	or 
	(SomeField not like "*groups*")
	or
	(SomeField not like "*upstream*") 
	or
	(SomeField not like "*upseed*")
)
There is no simple answer to your problem with the results. Almost certainly your criteria is broad enough that the whole table should be returned. You have to work it out from base principles. What are you trying to exclude? What do you actually want in the results?

For instance, (Like "ups*" or Not Like "Groups*" or not like "Upstream*") will return these records:

ups
groups
ups
Groups
ABC
XYZ
UpStream

Because Ups is not like groups*, and groups is not like Upstream*, and upstream is like Ups*, and ABC and XYZ are both not like Groups*. Or in other words, you probably will get the whole table back here.

Very likely what you are trying to do is not possible in one query.
 
Last edited:

kyleno

Board Regular
Joined
Jun 9, 2014
Messages
60
So I really only want to see results for Like "ups*" but exclude names that include groups or upstream etc. because they are not relevant to my search but will inevitably be brought in with the Like "ups*" query.

I recommend you put parentheses around your criteria with OR so as to keep the logic straight (this can be a cause of easy errors if you start to combine OR with AND later on). Also you need the field name when you use any criteria on a field.

Code:
... where 
(
    (SomeField like "ups*")
    or 
    (SomeField like "* ups*")
    or 
    (SomeField like "*ups*darm*")
    or 
    (SomeField not like "*upstate*") 
    or 
    (SomeField not like "*groups*")
    or
    (SomeField not like "*upstream*") 
    or
    (SomeField not like "*upseed*")
)
There is no simple answer to your problem with the results. Almost certainly your criteria is broad enough that the whole table should be returned. You have to work it out from base principles. What are you trying to exclude? What do you actually want in the results?

For instance, (Like "ups*" or Not Like "Groups*" or not like "Upstream*") will return these records:

ups
groups
ups
Groups
ABC
XYZ
UpStream

Because Ups is not like groups*, and groups is not like Upstream*, and upstream is like Ups*, and ABC and XYZ are both not like Groups*. Or in other words, you probably will get the whole table back here.

Very likely what you are trying to do is not possible in one query.
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
One possiblity is to first get the ups* results, then filter from the group (two steps).

Code:
select A.* 
from 
	(
	select * from TableFoo where FieldFoo like '*ups'
	) A
where 
	A.FieldFoo not like 'upstream*'
with this approach you can add what you like at the end:
Code:
select A.* 
from 
	(
	select * from TableFoo where FieldFoo like '*ups'
	) A
where 
	A.FieldFoo not like '*upstream*'
        and A.FieldFoo not like '*groups*'
        and A.FieldFoo not like '*zebras*'
        and A.FieldFoo not like '*giraffes*'

You will have to be very careful if you mix ANDs and ORs though - hopefully that won't be necessary.


From a purely technical standpoint, you can probably achieve this directly if you are careful and liberal with your use of parentheses:
Code:
select * 
from 
	TableFoo 
where 
	(
		(
			FieldFoo like '*ups'
		)
		and
		(
			FieldFoo not like '*upstream*'
			and FieldFoo not like '*groups*'
			and FieldFoo not like '*zebras*'
			and FieldFoo not like '*giraffes*'
		)
	)
 
Last edited:

Forum statistics

Threads
1,081,929
Messages
5,362,182
Members
400,671
Latest member
Tommy00836

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top