Criteria "Not this or that"

[mcgarr423]

Trying to modify a query to eliminated records with their status field being "Cancelled" and eliminate records with status "Completed"

The status field is a lookup with a set list of values, one being Cancelled and one Completed. I want those records to be omitted when this query is run.

[mcgarr423]

Should I be creating two queries? The first eliminating cancelled and then the second eliminating completed?

[aytee111]

And's and Or's tend to get a bit confusing when combined with "Not". You can use : Not In("Cancelled","Completed"). Another way is : <>"Cancelled" And <>"Completed".

[mcgarr423]

Thank you! My workaround for the time being was Not Like "C????????"

I thought it was a funny solution, both values just happen to start with the same letter and have the same # of characters... but that would confuse the hell out of people working on the database in the future haha

[mcgarr423]

Follow up... is there a way to omit "cancelled" and "completed" without also eliminating all records with that field Null? when using Not In("Cancelled","Completed") it seems to equate to not "completed", "cancelled", Null.

I have a default value for status now, but a lot of previous entries have a blank status field.

[JoeM]

Try:

Is Null Or Not In ("Cancelled","Completed")

[mcgarr423]

That did the trick, thank you