Hello! See the attached .zip for a word doc that includes all of the important images that go with this post. Image locations in the body text of this post will be marked as [image]
My question relates to filtering a query based on multiple ComboBoxes, and ignoring the filters when the ComboBoxes are left blank.
Please open the attached .zip and view my question there. The images are integral to my question.
[image]
The Goal:
I have a dashboard from which I would like to launch reports. The idea is to have 8 ComboBox filters (1) that apply to various reports (2). Eventually, I will have 10-15 reports based on queries and filtered based on these ComboBox selections.
The way the filters should work:
The ComboBox filters should engage when the ComboBox contains a value, but ignored when it is left blank.
What did NOT work:
I have tried various criteria to filter the queries upon which these reports are based. The one that seems like it definitely SHOULD work and would solve all of my problems can be seen here:
[image]
In this case, Combo109 is the ComboBox on my dashboard that filters based on ‘Region’. I know that the first part of this statement reads correctly, because when ComboBox109 has a selection, the records filter accordingly:
[image]
[image]
However, when the ComboBox is left empty – no data is returned:
[image]
I know this must be some issue with IIF statements in criteria. I know this because if I break out each possible result of the IIF statement and use them independently, the query runs fine:
[image]
…maddening. In any case, this form will not work, because this criteria returns no values when the ComboBox is left blank.
What worked a LITTLE bit:
I found a decent solution that did produce the desired results:
[image]
This filter applies the ComboBox selection as a filter UNLESS the ComboBox is left blank. Perfect! The only drawback: it automatically creates a second column as shown:
[image]
This works quite well for one or two criteria. However, exponentially more Criteria lines are generated as more and more filters are used. And I have 8 of them and could add some more. The result is an exponential increase in criteria lines:
[image]
…Not a sustainable solution.
As might be expected, this eventually leads to too many Critera rows and the Query grows too large:
[image]
Other problem with this method:
1.Difficult to revise.
a. As new filters are added, all filter fields must be deleted and re-added to type the original filter formula as shown:
b. =[Forms]![_Home]![Combo109] OR [Forms]![_Home]![Combo109] Is Null
So what should I do?
The internet has not been helpful. Most articles I have found focus on the FIRST solution, with the IIF statement, but nearly all peter out without an actual usable solution. The solution that I have been using works for up to 6 ComboBoxes, but is a terror to revise.
Perhaps I’m going about this an entirely wrong way? How would you filter your reports without making them too complex? I would love to hear your input and get myself to a better solution.
Thanks!