Conditional IIF in query criteria
Question posted by: Missionary
(Newbie)
on
August 27th, 2008 04:06 PM
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.
4
Answers Posted
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
-
SELECT [Field] FROM [Table] WHERE [Field] Like IIf([Forms]![frmNewE]![Arealu]="Blanks",Is Null Or "", ...);
try
-
SELECT [Field] FROM [Table] WHERE [Forms]![frmNewE]![Arealu]="Blanks" AND Nz([Field], "")="";
And so on with the rest two user choices.
Regards,
Fish
Thanks alot!
That worked like a prayer.
Now when it follows the "All" criteria:
- tblEquip.[Field] Like "*"
It doesn't include null values. Is there an easy way to include all records?
I came up with this:
- tblEquip.[Field] Like "*" OR Nz(tblEquip.[Field], "")=""
But it seems messy. Is there a better way?
Perfect. Thank you so much. This is working out better than I expected.
So now I have the following, which works perfectly:
- WHERE((([Forms]![frmNewE]![Arealu]="Blanks" AND (tblEquip.Area IS NULL OR tblEquip.Area=""))
-
OR ([Forms]![frmNewE]![Arealu]="All")
-
OR ([Forms]![frmNewE]![Arealu]="NonBlanks" AND ((tblEquip.Area) Is Not Null And Not (tblEquip.Area)=""))
-
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?
Quote:
Originally Posted by FishVal
 |
|
|
|
What is Bytes?
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 197,038 network members.
Top Microsoft Access / VBA Contributors
|
