not equal query

[Joyce]

I need to create a query that says display all records that meet the
following criteria:

- Field 1 (text) does not contain "OK" and does not contain "CD"
- Field 2 (yes/no) equals "yes"

I can get any one of them to work. However when I try to combine any of the
two, I just get the entire contents of the file as the result.

For field 1, many field values are simply, "OK". Other field values include
a statement like, "Not needed for CD". There are also other text statements
and there are blanks.

Thanks.

 

[fredg]

I need to create a query that says display all records that meet the
following criteria:

- Field 1 (text) does not contain "OK" and does not contain "CD"
- Field 2 (yes/no) equals "yes"

I can get any one of them to work. However when I try to combine any of the
two, I just get the entire contents of the file as the result.

For field 1, many field values are simply, "OK". Other field values include
a statement like, "Not needed for CD". There are also other text statements
and there are blanks.

Thanks.

As criteria on Field1:
Not Like "* OK *" and Not Like "* CD *"

As criteria on Field2, (on the same row as field1 criteria):
Yes

Note that the word Yes is not surrounded with quotes.

If you then look at the SQL View of the query, the Where clause will
read:

Where YourTable.[Field1] Not Like "* OK *" and YourTable.[Field1] Not
Like "* CD *" and YourTable.[Field2] = Yes

 

[KARL DEWEY]

Open your query in design view and in the Criteria row under Field1 enter
this --
Like "*OK*" AND Not Like "*CD*"

In the Criteria row (same row as above) under Field2 enter this --
True

 

[John W. Vinson]

Open your query in design view and in the Criteria row under Field1 enter
this --
Like "*OK*" AND Not Like "*CD*"

I'd go with Fred's suggestion, perhaps modified even a bit more. Searching for
two byte substrings in a freeform text field is fraught with danger! This
would exclude any records where field1 contains (say) "For sale in Oklahoma or
Texas" or "McDonald".

I'd suggest

NOT LIKE "OK *" AND NOT LIKE "* OK" AND NOT LIKE "* OK *" AND
NOT LIKE "CD *" AND NOT LIKE "* CD" AND NOT LIKE "* CD *"

to pick up strings beginning or ending with OK or CD (followed or preceded by
a blank) or containing the two letters as a "word". You'll still have some
errors (e.g. "This was OK, but..." will not be found).