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

Conditional IIF in query criteria

Missionary
P: 30
I have a form that displays data from a query, and I want to be able to control what data is displayed with a combo box on the form.
In the row source of the combo box, I have a list of values, but I also have some qualifiers (All, Blanks, NonBlanks, etc.)

I wrote a criterion that will show "All" or one of the values and it works well (see below.)

Like IIf([Forms]![frmNewE]![Arealu]="All","*",[Forms]![frmNewE]![Arealu])

But When I tried to include other types of qualifiers , I couldn't get it to work:

IIf([Forms]![frmNewE]![Arealu]="All",Like "*",IIf([Forms]![frmNewE]![Arealu]="Blanks",Is Null Or "",IIf([Forms]![frmNewE]![Arealu]="NonBlanks",Is Not Null And Not "" , Like [Forms]![frmNewE]![Arealu])))

It seems like you can't have a "Like" statement within an IIF Statement, but I'm not sure.
Can anyone help me with this syntax? Much Thanks.
Aug 27 '08 #1
Share this Question
Share on Google+
5 Replies


FishVal
Expert 2.5K+
P: 2,653
Hello.

Database engine is not supposed to run functions inside SQL expression to get missing part(s) of SQL expression itself. ;)

I see that your problem is that you couldn't figure out what IIf() should return as an argument to Like operator in order to fetch records with Null value in the field being filtered by.

I suggest you to change query syntax:
instead of
Expand|Select|Wrap|Line Numbers
  1. SELECT [Field] FROM [Table] WHERE [Field] Like IIf([Forms]![frmNewE]![Arealu]="Blanks",Is Null Or "", ...);
  2.  
try
Expand|Select|Wrap|Line Numbers
  1. SELECT [Field] FROM [Table] WHERE [Forms]![frmNewE]![Arealu]="Blanks" AND Nz([Field], "")="";
  2.  
And so on with the rest two user choices.

Regards,
Fish
Aug 27 '08 #2

Missionary
P: 30
Thanks alot!
That worked like a prayer.

Now when it follows the "All" criteria:
Expand|Select|Wrap|Line Numbers
  1. tblEquip.[Field] Like "*" 
It doesn't include null values. Is there an easy way to include all records?

I came up with this:
Expand|Select|Wrap|Line Numbers
  1. tblEquip.[Field] Like "*" OR Nz(tblEquip.[Field], "")=""
But it seems messy. Is there a better way?
Aug 27 '08 #3

FishVal
Expert 2.5K+
P: 2,653
Oh, yes.

Criteria like
Expand|Select|Wrap|Line Numbers
  1. ... WHERE [Forms]![frmNewE]![Arealu]="All" ...
  2.  
will enforce fetching all records in a case user choice is "All"

Regards,
Fish

P.S. You may read a similar thread - How to show all values in a query iif criteria?
Aug 28 '08 #4

Missionary
P: 30
Perfect. Thank you so much. This is working out better than I expected.

So now I have the following, which works perfectly:

Expand|Select|Wrap|Line Numbers
  1. WHERE((([Forms]![frmNewE]![Arealu]="Blanks" AND (tblEquip.Area IS NULL OR tblEquip.Area="")) 
  2. OR ([Forms]![frmNewE]![Arealu]="All") 
  3. OR ([Forms]![frmNewE]![Arealu]="NonBlanks" AND ((tblEquip.Area) Is Not Null And Not (tblEquip.Area)="")) 
  4. OR (tblEquip.Area like [Forms]![frmNewE]![Arealu])) )
The problem is that in the form that I am using, there are 18 fields that I am sorting by. So I put in similar code in the query for each field. And this works fine until I get too many of them, then I get an error message, that says the expression is too complex to be evaluated and that I should simplify the code by assigning parts of the expression to variables.

Any thoughts on how I can simplify it?


Oh, yes.

Criteria like
Expand|Select|Wrap|Line Numbers
  1. ... WHERE [Forms]![frmNewE]![Arealu]="All" ...
  2.  
will enforce fetching all records in a case user choice is "All"

Regards,
Fish

P.S. You may read a similar thread - How to show all values in a query iif criteria?
Aug 28 '08 #5

P: n/a
FishVal: Thank you so much for that! I was doing the exact same problem trying to work a query that I wanted to search for the First or Last Name, but I wanted to let the user pick either field from the Search Modal Dialog. You just saved me from going crazy.
Nov 12 '10 #6

Post your reply

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