IIF statement in query criteria based on checkbox

[Xenver]

ok, so i have a form with unbound check boxes on it, i want to alter a query criteria based on the check boxes values,so i have this as an example of my query criteria:

IIf([Forms]![frm_Reports Credit]![SS 1]=True,>0,Null)

SS 1 is the check box I'm trying to evaluate.>0 is what i want the query criteria to be if the box is checked, Null is the value i want the criteria to be if the box is unchecked.

The statement i have now runs, but returns no results.

[Seth Schrock]

I'm pretty sure that when I have done this that I included the whole criteria in the IIF statement. For example

WHERE IIF(Forms![frm_Reports Credit]![SS 1]=True
          , MyField > 0
          , MyField Is Null)

[NeoPa]

Absolutely right Seth :-)

IIf() is a function that returns a value. The value can be used within SQL code but it cannot return SQL code itself as such.

[twinnyfo]

However, wouldn't the result of the iif be in quotes, since it is being added to a SQL statement?

[NeoPa]

No. The result would be a value certainly, but not necessarily a string.

MyField > 0 and MyField Is Null are both expressions that return boolean values. As the WHERE clause in SQL actually works with boolean values - even though most of us are more used to seeing expressions that return boolean values - this code works fine.

[twinnyfo]

Aaaaahahhhh, I think I get it now. So, in theory, one could simply say

WHERE True

And all records would be returned or

WHERE False

And no records would be returned?

Once again, I have learned something useful here on this forum. Thanks for your insight!

[NeoPa]

Absolutely. That's a perfect understanding.

That's also why one of my favourite techniques using the Select Case statement in VBA, which makes it way more flexible, is to say :

Select Case True
Case X = 3
    ...
Case Y = "Bob"
    ...
End Select

[Xenver]

Thanks Guys, you were all very helpful.