Search code examples
sqlms-accessiif

Microsoft Access Query Should return true or true and false, only returns true

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.

Solution

  • 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.