NOT LIKE query in Access 2013 not working

kyleno

Board Regular
Joined
Jun 9, 2014
Messages
61
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.
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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:
Upvote 0
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.
 
Upvote 0
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:
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

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
Back
Top