Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

blanket condition over subreports

Discussion in 'Information Technology' started by Mark1, Jul 28, 2006.

  1. Mark1

    Mark1
    Expand Collapse
    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]
     
  2. Loading...


  3. Ken Sheridan

    Ken Sheridan
    Expand Collapse
    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

    "Mark1" wrote:

    > 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]
     

Share This Page