NOT LIKE query in Access 2013 not working

[kyleno]

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]

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.

... 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.

 

[kyleno]

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.

... 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]

One possiblity is to first get the ups* results, then filter from the group (two steps).

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:

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:

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*'
		)
	)