Try to retrieve All records using an iif statement

jcaulfield

Board Regular
Joined
Nov 23, 2012
Messages
50
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.

IIf([forms].[sourcesSelect].[Cbx_IncExp]="All",<>"zzz",[forms].[sourcesSelect].[Cbx_IncExp])
IIf([Forms].[SourcesSelect].[Cbx_IncExp]="All",Is Not Null,[Forms].[SourcesSelect].[Cbx_IncExp])

<tbody>
</tbody>


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.
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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

Board Regular
Joined
Nov 23, 2012
Messages
50
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

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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

Well-known Member
Joined
Jan 11, 2009
Messages
1,210
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"
 
Last edited:

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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

Active Member
Joined
Jul 16, 2010
Messages
474
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.
 
Last edited:

Forum statistics

Threads
1,175,486
Messages
5,897,689
Members
434,673
Latest member
kktfc

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top