Pass Operator to Criteria in Query

[Michael]

Hi Folks - I have a status field in a query that contains "open", "closed",
"pending" and "NA". I need a way to pass criteria to this field with the
following logic:

Pass "open" to the field
Pass NOT "open" to the field.

The first option is easy. However, I'm not sure how to pass the logical
operator NOT. Any thoughts. Thanks.

Michael

 

[KARL DEWEY]

Why not use a form with you selection input using an Option Group?
WHERE ([Status] = "Open" And [Forms]![YourForm]![Frame1] = 1) OR ([Status]
<> "Open" And [Forms]![YourForm]![Frame1] = 2) OR ([Status] Like "*" And
[Forms]![YourForm]![Frame1] = 3)

Option 3 records.

 

[John Spencer]

If you are using a parameter then try entering the follow in the criteria

Field: Status
Criteria(1): = [What Status]
Criiteria(2): <> "Open" AND [What Status] <> "Open"

If What Status is "Open" then you will get just the records that are "Open".
If What Status is anything else you will get all records that aren't "Open"

Access will rearrange this and for you after you save the query, but it should
still work.

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