B
Brian
For some reports, I prefer to write a single report and give the user up to
ten or more filters (controls, usually combo boxes) on the form that calls
the report). I then drop a reference to the control name in the query's
criteria line to filter based on that control if it is populated.
Since I have, by default, only nine criteria lines to a query, I sometimes
create two or three queries that each filter out different elements, and then
do a join between them to apply ALL filters.
However, I just ran into my first need to use a list box for one of the
criteria (or at least a need to stipulate "Equals" or "Does not equal" as an
option), and the construction of the resultant query in VBA looks like a
garganuan task. Does anyone have a better approach to multi-filtered reports,
or am I just crazy to try to offer this type of flexibility?
Just as an example, here is the SQL view of one of three such stacked
queries that it takes to run one VERY flexible report, and yes, I did this
with the query builder, not by manually writing the SQL:
SELECT Contracts.ContractID
FROM ReportContractBase1 INNER JOIN Contracts ON
ReportContractBase1.ContractID = Contracts.ContractID
WHERE (((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
(((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
((([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null));
ten or more filters (controls, usually combo boxes) on the form that calls
the report). I then drop a reference to the control name in the query's
criteria line to filter based on that control if it is populated.
Since I have, by default, only nine criteria lines to a query, I sometimes
create two or three queries that each filter out different elements, and then
do a join between them to apply ALL filters.
However, I just ran into my first need to use a list box for one of the
criteria (or at least a need to stipulate "Equals" or "Does not equal" as an
option), and the construction of the resultant query in VBA looks like a
garganuan task. Does anyone have a better approach to multi-filtered reports,
or am I just crazy to try to offer this type of flexibility?
Just as an example, here is the SQL view of one of three such stacked
queries that it takes to run one VERY flexible report, and yes, I did this
with the query builder, not by manually writing the SQL:
SELECT Contracts.ContractID
FROM ReportContractBase1 INNER JOIN Contracts ON
ReportContractBase1.ContractID = Contracts.ContractID
WHERE (((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.Status)<>[Forms]![MenuReports]![ContractStatus]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Not Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
(((Contracts.ContractType)=[Forms]![MenuReports]![ContractType]) AND
(([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Not Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
(((Contracts.ContractID)=[Forms]![MenuReports]![ContractID]) AND
(([Forms]![MenuReports]![ContractID]) Is Not Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null)) OR
((([Forms]![MenuReports]![ContractID]) Is Null) AND
(([Forms]![MenuReports]![ContractType]) Is Null) AND
(([Forms]![MenuReports]![ContractStatus]) Is Null));