Try to retrieve All records using an iif statement

[jcaulfield]

I am trying to run a query using criteria from a form. The criteria has a combo Box with 3 options All, Expense, or Income. With the following 2 formulas I get the correct results if I select "Income" or "Expense", but get nothing if select all.

[TABLE="class: cms_table, width: 675"]
<tbody>[TR]
[TD]IIf([forms].[sourcesSelect].[Cbx_IncExp]="All",<>"zzz",[forms].[sourcesSelect].[Cbx_IncExp])[/TD]
[/TR]
[TR]
[TD]IIf([Forms].[SourcesSelect].[Cbx_IncExp]="All",Is Not Null,[Forms].[SourcesSelect].[Cbx_IncExp])
[/TD]
[/TR]
</tbody>[/TABLE]


If I eliminate the formula and enter <>"zzz" or "Is not Null", I get all records. Why doesn't my iif statement work? suggestion greatly appreciated.

Any help would be greatly appreciated.

Sincerely,

Stumped.

 

[xenou]

If I eliminate the formula and enter <>"zzz" or "Is not Null", I get all records. Why doesn't my iif statement work? suggestion greatly appreciated.

Is all records what you want when you select all, because that sounds like what you want.

 

[jcaulfield]

Is all records what you want when you select all, because that sounds like what you want.

But I also want to get just the Income records or the Expense records if I select one of the other combo box options.

 

[xenou]

You said you get correct results for Income and Expense records:

The criteria has a combo Box with 3 options All, Expense, or Income. With the following 2 formulas I get the correct results if I select "Income" or "Expense", but get nothing if select all.

I think you need to clarify or otherwise restate your problem more clearly.

 

[james_lankford]

he wants a query to return all records

when he specifically states <> "zzz" or "Is not Null" as the criteria then he gets all records

but now he wants the query to get it criteria from a combobox

but he wants to keep "all" as an option in the combobox

so he made that formula

if the combobox choice = "all" then use the same criteria that worked for him before ---- <> "zzz" or "Is not Null" ----

but if the combobox choice does not = "all" then use the combobox choice as the criteria

but when he chooses "all" in the combobox no recorsds are returned

his question is "why does the criteria work when stated explicitly, but not when used in his formula"

 

[xenou]

Hmm, I would still need to see the rest of it. Like how are either of these expressions actually being used? Are they part of a query? What is the query?

 

[stumac]

Iif statements don't work like this in criteria of the Query when additional operators are added in - Not sure why, above my intelligence level.... but instead try this

In the Criteria put: [forms].[sourcesSelect].[Cbx_IncExp] Or [forms].[sourcesSelect].[Cbx_IncExp] = "All"

this way if you select any value in the combo the query will limit its results to what the combo box shows unless you select All in which case it shows all records.