blanket condition over subreports

[Guest]

I have a macro that runs a report. Within that report are 6 subreports that
are created from two queries a piece. I need two versions of the parent
report. One "plain" and one with a condition blanketed over all subreports.
So, I've tried putting my condition in the "Where:" field on the macro
screen, for the parent report, thinking that if I have a where condition for
the parent report it will apply to the subreports. However, either I am
doing something wrong or its not being applied to the subreports. Does
anybody have any suggestions? Thanks!

If I wrote my condition in SQL it would be something like this:
WHERE UNIT = [FORM1]![ComboBox1]

 

[Guest]

You could open the report from a button on a dialogue form which includes a
control for entering or selecting the Unit value on which you want to
restrict the subreports. The RecordSources of the subreports would then be
queries which reference the control but also test for it being NULL, e.g.

SELECT *
FROM MyTable
WHERE Unit = Forms!MyForm!cboMyComboBox
OR Forms!MyForm!cboMyComboBox IS NULL;

If the control on the form is left blank (NULL) the WHERE clause of each
query would evaluate to TRUE and return all rows; if the control has a value
then the WHERE clause would only evaluate to TRUE only for rows where the
value of the Unit column matched the value in the control, returning only
those rows.

If the value for the Unit column is a constant, 123 say, then another way
would be to have a 'Restrict Subreports to 123 Unit Values' check box on the
form instead of a control to enter the value. The queries would then go like
this:

SELECT *
FROM MyTable
WHERE (Unit = 123 AND Forms!MyForm!chkMyCheckBox)
OR NOT Forms!MyForm!chkMyCheckBox;

If the check box is checked (TRUE) the WHERE clause would evaluate to TRUE
for rows where the value of the Unit column is 123; if its unchecked (FALSE)
then it would evaluate to TRUE for all rows.

If Unit is a text field rather than a number you'd wrap the value in quotes
in the query: "123".

Ken Sheridan
Stafford, England