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.
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. 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
Joined
Mar 2, 2007
Messages
16,792
Office Version
  1. 2019
Platform
  1. 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:

Watch MrExcel Video

Forum statistics

Threads
1,122,228
Messages
5,594,934
Members
413,953
Latest member
Arthur1471

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top