sql - Query Filter: IIf with return >= case

Question

CIAO!

I need to create a Query filtered in this way:

IIf([Forms]![Discipline_Bah]![F]=">=1",>=1, like [Forms]![Discipline_Bah]![F] )

But the >=1 return always 0.

How can I solve this? thanks for all the answers!

Edit: added more details: I've a form with a ComboBox with the parameters 0; >=1; I wish to filter the data in a query, according to value present in the ComboBox. (the combobox is a string)

iif([combobox]=">=1", then filter >=0; else filter =[combobox])

question from:https://stackoverflow.com/questions/66061179/query-filter-iif-with-return-case

Answer 1

Operators (>=, =, etc) cannot be dynamic in query object. Have to use a static operator and then IIf() determines what parameter to return.

Consider:

WHERE fieldname LIKE IIf(combobox = "All", "*", combobox)

If your requirements are really more complicated than indicated, advise not to use dynamic query object. Use VBA to conditionally build filter criteria and apply to form or report. Or use VBA to manipulate QueryDefs to modify query object.