Eliminate results based on criteria - not working

[Mary]

I am trying to create a query that will eliminate people living in group
homes. I put this together in the grid format. However, when I write this
query it is ignoring what I am requesting. If I limit by one home it will
remove it, but if I limit by more than one then it doesn't eliminate any of
the homes. Here is what I have:

SELECT tblCustomerAddresses.IndexName, tblCustomerAddresses.CustomerType,
tblCustomerAddresses.HomeAddress1, tblCustomerAddresses.HomeAddress2,
tblCustomerAddresses.HomeAddress3, tblCustomerAddresses.HomeCity,
tblCustomerAddresses.HomeState, tblCustomerAddresses.HomeZip,
tblCustomerAddresses.Country
FROM tblCustomerAddresses
WHERE (((tblCustomerAddresses.CustomerType) Like "member*") AND
((tblCustomerAddresses.HomeAddress1) Not Like "*motherhouse*" Or
(tblCustomerAddresses.HomeAddress1) Not Like "*mapleway*" Or
(tblCustomerAddresses.HomeAddress1) Not Like "*center*"));

Suggestions are very appreciated.

 

[vanderghast]

Try to change your OR to AND.

Vanderghast, Access MVP

 

[John W. Vinson]

I am trying to create a query that will eliminate people living in group
homes. I put this together in the grid format. However, when I write this
query it is ignoring what I am requesting. If I limit by one home it will
remove it, but if I limit by more than one then it doesn't eliminate any of
the homes. Here is what I have:

SELECT tblCustomerAddresses.IndexName, tblCustomerAddresses.CustomerType,
tblCustomerAddresses.HomeAddress1, tblCustomerAddresses.HomeAddress2,
tblCustomerAddresses.HomeAddress3, tblCustomerAddresses.HomeCity,
tblCustomerAddresses.HomeState, tblCustomerAddresses.HomeZip,
tblCustomerAddresses.Country
FROM tblCustomerAddresses
WHERE (((tblCustomerAddresses.CustomerType) Like "member*") AND
((tblCustomerAddresses.HomeAddress1) Not Like "*motherhouse*" Or
(tblCustomerAddresses.HomeAddress1) Not Like "*mapleway*" Or
(tblCustomerAddresses.HomeAddress1) Not Like "*center*"));

Suggestions are very appreciated.

"Not" logic and "Or" logic don't mix very well. You're getting all the hits
because a record with "motherhouse" in the HomeAddress1 field does NOT have
mapleway in that field - so the expression

(tblCustomerAddresses.HomeAddress1) Not Like "*mapleway*"

is in fact TRUE, therefore the entire expression is TRUE.

Try using parentheses to block the different exclusion criteria:

WHERE tblCustomerAddresses.CustomerType Like "member*"
AND NOT
(tblCustomerAddresses.HomeAddress1 Like "*motherhouse*" Or
tblCustomerAddresses.HomeAddress1 Like "*mapleway*" Or
tblCustomerAddresses.HomeAddress1 Like "*center*");