By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,238 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

IIF statement in query criteria based on checkbox

P: 20
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:

Expand|Select|Wrap|Line Numbers
  1. 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.
Aug 14 '14 #1

✓ answered by Seth Schrock

I'm pretty sure that when I have done this that I included the whole criteria in the IIF statement. For example
Expand|Select|Wrap|Line Numbers
  1. WHERE IIF(Forms![frm_Reports Credit]![SS 1]=True
  2.           , MyField > 0
  3.           , MyField Is Null)

Share this Question
Share on Google+
7 Replies


Seth Schrock
Expert 2.5K+
P: 2,941
I'm pretty sure that when I have done this that I included the whole criteria in the IIF statement. For example
Expand|Select|Wrap|Line Numbers
  1. WHERE IIF(Forms![frm_Reports Credit]![SS 1]=True
  2.           , MyField > 0
  3.           , MyField Is Null)
Aug 14 '14 #2

NeoPa
Expert Mod 15k+
P: 31,492
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.
Aug 14 '14 #3

twinnyfo
Expert Mod 2.5K+
P: 3,284
However, wouldn't the result of the iif be in quotes, since it is being added to a SQL statement?
Aug 14 '14 #4

NeoPa
Expert Mod 15k+
P: 31,492
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.
Aug 15 '14 #5

twinnyfo
Expert Mod 2.5K+
P: 3,284
Aaaaahahhhh, I think I get it now. So, in theory, one could simply say

Expand|Select|Wrap|Line Numbers
  1. WHERE True
And all records would be returned or

Expand|Select|Wrap|Line Numbers
  1. WHERE False
And no records would be returned?

Once again, I have learned something useful here on this forum. Thanks for your insight!
Aug 15 '14 #6

NeoPa
Expert Mod 15k+
P: 31,492
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 :
Expand|Select|Wrap|Line Numbers
  1. Select Case True
  2. Case X = 3
  3.     ...
  4. Case Y = "Bob"
  5.     ...
  6. End Select
Aug 18 '14 #7

P: 20
Thanks Guys, you were all very helpful.
Aug 18 '14 #8

Post your reply

Sign in to post your reply or Sign up for a free account.