Query Expression

[Karen]

I created a query to remove results from three fields that contain certain
text. This text is can be two variations, with or without a dash. When I
add the criteria to the third field (Address1), I don't get the correct
results.

I pulled up the sql view and this is the statement:
SELECT LastName, OfficeName, Address1, FROM Doctors
WHERE (((LastName) Not Like "*Xxx-Xxxx*" Or (LastName) Not Like
"*Xxxxxxx*")) OR (((OfficeName) Not Like "*Xxx-Xxxx*" Or (OfficeName) Not
Like "* Xxxxxxx *")) OR (((Address1) Not Like "*Xxx-Xxxx*" Or (Address1) Not
Like "*Xxxxxxx*"));

I am not sure how to write the expression for each of those three fields.

 

[Karen]

I also don't get correct results when I add the criteria to the second field
(Office Name). The criteria works for each field individually, but not when
I try to use it in all three fields.

 

[John Spencer]

Boolean logic would require you to use AND not OR between the not like for
each group

SELECT LastName, OfficeName, Address1
FROM Doctors
WHERE (LastName Not Like "*Xxx-Xxxx*" AND LastName Not Like "*Xxxxxxx*") OR
(OfficeName Not Like "*Xxx-Xxxx*" AND OfficeName Not Like "* Xxxxxxx *") OR
(Address1 Not Like "*Xxx-Xxxx*" AND Address1 Not Like "*Xxxxxxx*")

If you want to exclude records where any of the three fields match the
pattern you have then you need to change the remaining OR to AND also.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Karen]

Thanks - I changed OR to AND and the text is still in the results. Here is
what I pulled from the View SQL. Still not sure what I am doing wrong. When
I enter the criteria for one field, such as LastName, it works. When I add
the criteria to the next field, OfficeName, it does not pull out those names
with the criteria.

SELECT LastName, OfficeName, Address1 FROM Doctors
WHERE (((LastName) Not Like "*Xxx-Xxxx*" And (LastName) Not Like
"*Xxxxxxx*")) OR (((OfficeName) Not Like "*Xxx-Xxxx*" And (OfficeName) Not
Like "*Xxxxxxx*")) OR (((Address1) Not Like "*Xxx-Xxxx*" And (Address1) Not
Like "*Xxxxxxx*"));

 

[John Spencer]

Try some tests to pinpoint the cause of the problem. Break the query down
and look at one set of criteria at a time to see what might be happening.

Try this query which only screens records based on OfficeName and see if it
works to return the expected set of records.
SELECT LastName, OfficeName, Address1 FROM Doctors
WHERE OfficeName Not Like "*Xxx-Xxxx*" And OfficeName Not
Like "*Xxxxxxx*"

If not, then we need to examine what is stored in OfficeName. Is it
possible that you have used a lookup field for this. In the query datasheet
view soe the OfficeName field have a drop-down combo box so you can choose
an item? If so, then what is stored in the field is NOT what is probably
NOT what is being displayed.

If you do get back the expected records (and only the expected records) then
we need to look more closely at the original query.
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Karen]

I tried adding the criteria to one field at a time and it worked separately
for each field. I did find out that I have some null fields in the Office
Name. I can get the correct results for LastName and OfficeName by using the
following:
SELECT LastName, OfficeName, Address1
FROM Doctors
WHERE (((OfficeName) Not Like "*Wal-Mart*" And (OfficeName) Not Like
"*Walmart*")) OR (((LastName) Not Like "*Wal-Mart*" And (LastName) Not Like
"*Walmart*") AND ((OfficeName) Is Null));

I can't figure how to use the same criteria in the Address1 field. I need
to remove those records as well with the same criteria.