Search criteria for words in the "middle" of text string

[Toan]

I'm using Microsoft Access 2007.

I have a table with this field : Dealership Names

Here are some examples of the values in the field...

Honda of California
John Jake Honda/Toyota
Super Honda of Southern CA


I want to build a query to filter out any dealership that contains the word
"Honda" in their name.

In the criteria (of the query) I've tried *Honda and Honda* but I can't seem
to filter out the one that has "Honda" in the middle of the name.

Can someone please tell me the criteria I need to use to exclude all names
with the word "Honda" in it?

A thousand thanks in advance!

 

[John Spencer]

Try entering the following as the criterion:
Not Like "*honda*"

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Toan]

Hi John,

That did work. But how do I do it for multiple criteria?

For example...

John Ford Trucks
Henry Honda Cars

If I use only Not Like "*Ford*", it does remove John Ford Trucks.

But if I use .....

Not Like "*Ford*" or Not Like "*Honda*", it removes neither.

Can you please tell me how I can apply multiple Not Like "*XXX*" criteria?

Thanks for responding

 

[Bob Barrows]

Use AND instead of OR ... OR returns True if either comparison is true.

To see what I'm talking about, apply the OR'ed comparisons to "John Ford
Trucks".
Not Like "*Ford*" returns false as you would expect.
Not Like "*Honda*" returns true, also as you would expect.
An expression built with OR returns True if either is is true, so, the
record does not get eliminated.
Using AND to build the expression instead, both operands must be true in
order for it to return true. So, since one of the comparisons is false,
the AND expression returns False, asnd the row is eliminated as you
want.

Alternatively, if it helps get your head around it, put the OR'ed LIKE
comparisons inside parentheses, and put the NOT keyword outside the
parentheses. You will need to use SQL View to do this:

.... AND Not (Fieldname Like "*Ford*" or Fieldname Like "*Honda*")

 

[John Spencer]

Boolean logic requires you to use AND instead of OR so both conditions must be
true to return a record. With OR only one condition has to be true and John
Ford Trucks is not like *Honda* so it is returned and Henry Honda Trucks is
Not Like *ford* so it is true.

WIth AND John Ford Trucks is Like *Ford* (True) but you negate that with the
NOT operator to False and if one evaluation is false in a series of ANDs then
the entire expression is False and therefore John Ford Trucks is not returned.

So use
SomeField Not Like "*Ford*" AND SomeField Not Like "*Honda*"

Or you can restate that as
Not (SomeField Like "*Ford*" or SomeField Like "*Honda*")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Toan]

Great thanks!

Boolean logic requires you to use AND instead of OR so both conditions must be
true to return a record. With OR only one condition has to be true and John
Ford Trucks is not like *Honda* so it is returned and Henry Honda Trucks is
Not Like *ford* so it is true.

WIth AND John Ford Trucks is Like *Ford* (True) but you negate that with the
NOT operator to False and if one evaluation is false in a series of ANDs then
the entire expression is False and therefore John Ford Trucks is not returned.

So use
SomeField Not Like "*Ford*" AND SomeField Not Like "*Honda*"

Or you can restate that as
Not (SomeField Like "*Ford*" or SomeField Like "*Honda*")


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[Toan]

Thanks!

That worked!

Use AND instead of OR ... OR returns True if either comparison is true.

To see what I'm talking about, apply the OR'ed comparisons to "John Ford
Trucks".
Not Like "*Ford*" returns false as you would expect.
Not Like "*Honda*" returns true, also as you would expect.
An expression built with OR returns True if either is is true, so, the
record does not get eliminated.
Using AND to build the expression instead, both operands must be true in
order for it to return true. So, since one of the comparisons is false,
the AND expression returns False, asnd the row is eliminated as you
want.

Alternatively, if it helps get your head around it, put the OR'ed LIKE
comparisons inside parentheses, and put the NOT keyword outside the
parentheses. You will need to use SQL View to do this:

.... AND Not (Fieldname Like "*Ford*" or Fieldname Like "*Honda*")