Conditional IIF in query criteria

[Missionary]

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.

[FishVal]

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

[Missionary]

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?

[FishVal]

Oh, yes.

Criteria like

... WHERE [Forms]![frmNewE]![Arealu]="All" ...
 

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?

[Missionary]

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?

Oh, yes.

Criteria like

... WHERE [Forms]![frmNewE]![Arealu]="All" ...
 

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?

[TsukiakariUsagi]

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.