How to exclude certain records

[Guest]

How can I create a query to exclude records containing certain words like the
word "accept". If I have multiple fields containing this word "accept" and
"fail" how can I exclude all records with these words from each field?

I have 3000 records with 5 fields that have this word in many records. I
only want to see records that do not have these words "accept" and "fail". I
also have many records with blank fields in any of the 5 fields. I don't
want blank records to affect my sort/criteria accuracy. I've tried using not
"accept" and not "fail" as a criteria but this does not work accurately when
I move over to next field. Also, when should criteria be on one line in
design view and when should criteria be typed into two lines (one above the
other)?

Using Access 2000 with Windows XP.

 

[Guest]

In the criteria for the first appropriate field, type in:

Not In ("accept","fail")

Then under the next field, but one line down, put in the above criteria.
Stair-step the criteria until all the appropriate fields are covered.

However if even one of these fields have accept or fail, the entire record
will not be returned.

 

[Guest]

Jerry, I'll try this and let you know shortly. Thanks!

 

[John W. Vinson]

How can I create a query to exclude records containing certain words like the
word "accept". If I have multiple fields containing this word "accept" and
"fail" how can I exclude all records with these words from each field?

If the records you want to exclude have either "accept" or "fail" as the
*only* content of the field, you can use a criterion

NOT IN("accept", "fail") OR IS NULL

If you want to exclude records where these words occur anywhere in the field,
among other text, you can use

(NOT LIKE "*accept*" AND NOT LIKE "*fail*") OR IS NULL

John W. Vinson [MVP]

 

[Guest]

John and Jerry,

There is a problem when I enter your suggested criteria under the second
field. It works only on one field. When I step down a line within criteria
and enter the same code the results negatively affect my first field. Those
unwanted words show up again. Now I have more records rather than fewer
records. Any suggestions. And, yes did use John's suggestion too because
some of these words I'm trying to ommit are within a short phrase. So I do
have to use the wild card *.

 

[Michel Walsh]

Try one a single line of criteria, rather than stepping down on different
line for each fields.


Vanderghast, Access MVP

 

[Guest]

That does work! I put the criteria on a single line in each field. Thanks
to all of you for your help. Where can I read up on using criteria on single
line vs. in a step down pattern? Or, even using mulitple criteria lines
under a single field. How do all these affect my results? Thanks again.

 

[John W. Vinson]

That does work! I put the criteria on a single line in each field. Thanks
to all of you for your help. Where can I read up on using criteria on single
line vs. in a step down pattern? Or, even using mulitple criteria lines
under a single field. How do all these affect my results? Thanks again.

It may help to look at the SQL view of the query that you create. That's the
*real* query - the grid is just a tool to help you build SQL strings.

Criteria on the same line are combined using AND logic - *all* of the criteria
on the same line must be satisfied for the record to be retrieved.

Criteria on different lines use OR logic - the record will be retrieved if
*any one* of the criteria are satisfied.

Some queries will need a combination of OR logic and AND logic; this can get
beyond the capabilities of the grid, and you'll need to go into SQL and use
parenthesis nesting to specify the precise search that you want.

John W. Vinson [MVP]