Welcome to SPN

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

Sign Up Now!
  1. Guest ji, please consider donating today!   Become a Supporter    ::   Make a Contribution   
    Monthly (Recurring) Target: $300 :: Achieved: $95

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]
     
Since you're here... we have a small favor to ask...

More people are visiting & reading SPN than ever but far fewer are paying to sustain it. Advertising revenues across the online media have fallen fast. So you can see why we need to ask for your help. Donating to SPN's is vote for free speech, for diversity of opinions, for the right of the people to stand up to religious bigotry. Without any affiliation to any organization, this constant struggle takes a lot of hard work to sustain as we entirely depend on the contributions of our esteemed writers/readers. We do it because we believe our perspective matters – because it might well be your perspective, too... Fund our efforts and together we can keep the world informed about the real Sikh Sikhi Sikhism. If everyone who writes or reads our content, who likes it, helps us to pay for it, our future would be much more secure. Every Contribution Matters, Contribute Generously!

    Become a Supporter      ::     Make a Contribution     



Share This Page