I am making an access query . There is a field with a Yes/No box. The query criteria parameter is IIf([Type True or All]="True",-1,>-2)
. But even if it evaluates to false, it only returns yes, which in access is -1, not both yes and no, which in Access is -1 and 0. If I set the criteria as >-2, then it returns both Yes and No.
Operators (=, >, LIKE, etc) cannot be dynamic. The operator must be static, then IIf() returns parameter.
Could do something like:
WHERE fieldname LIKE IIf([Type True or All] = "True", -1, "*")
Or for less typing by user:
WHERE fieldname LIKE IIf([Type "T" for True or "A" for All] = "T", -1, "*")
.
Or this:
WHERE fieldname LIKE Choose([Type 1 for True, 2 for False, 3 for All], -1, 0, "*")
Or for text field:
WHERE fieldname LIKE IIf([enter value or ALL]="ALL", "*", [enter value or ALL])
I don't use dynamic parameterized queries - I prefer VBA to build criteria and apply to form or report. Especially advise not to use popup inputs because they cannot be validated, inputs on form can.