Query Criteria IIF statement - using Not Equal when True/False

[dcdimon]

I have been searching for an hour without success for an answer to my question.



I have an iif statement as a criteria in a query. When the test is true then I want to show all records where the field is NOT equal to "none", otherwise show me everything. I'm using the following as my iif:

Like IIf([forms]![DefectNotificationReviewF]![DefectFilterOpt]=2,<>"None",[NotificationNumber])

[NotificationNumber] is the field name. When I pull out the <> it shows me all the records with NONE in the field. However, for my purpose I need to see the records without NONE. Those records contain unique number strings so I can't search for a single item. I need to ignore the NONE entries to show the set of records.

Can anyone point me in the right direction?

Thanks,
Dave D.

[June7]

LIKE operator is only useful with wildcard and with text type fields, not numbers.

The operator (LIKE, <>, =, etc) cannot be dynamic. Mixing LIKE with another operator won't work.

I never use dynamic parameterized queries. I prefer VBA to construct filter criteria. Review: http://www.allenbrowne.com/ser-62.html

If you must use parameterized query, then calculate a field in query:

MyCriteria: IIf([forms]![DefectNotificationReviewF]![DefectFilterOpt]=2 AND [NotificationNumber]="None",False,True)

Filter criteria under that field: True

[dcdimon]

I ended up making a calculated field in the query - as June7 suggested - about 10 minutes after I posted this question. I think I needed to take a break from staring at the query builder because as soon as I went back in I thought of it.

I had toyed with the idea of using a SQL VBA script, but I wanted something that i could easily modify and use some form data to filter with. The VBA was a bit too cumbersome for that.

DD