Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Display Main Report if Subreport Has Data (2007 SP1)

    I have found lots of posts regarding having a subreport display when there is no data, but how about the reverse?

    I am setting up a report that will display contributions from previous years in the main report, while the subreport displays contributions from the current year. Since the database is brand new this year, there will not be any "previous years". Any suggestions for how to handle this situation?

    Thank you.
    Attached Files Attached Files
    egghead

  2. #2
    Star Lounger
    Join Date
    Mar 2007
    Location
    Dexter, USA
    Posts
    90
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display Main Report if Subreport Has Data (2007 SP1)

    PLEASE copy me on the answer. I was about to post the same thing. Thanks. tcarriero@thetford.com

  3. #3
    5 Star Lounger
    Join Date
    Nov 2004
    Location
    Wilmington, North Carolina, USA
    Posts
    1,196
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display Main Report if Subreport Has Data (2007 SP1)

    Since you've already got a report for the current year, why can't you just use that? I'd create a third report for this one-time use. Seems to be the quickest and most practical, since this situation shouldn't ever rise again.
    ____________________________
    Jeremy
    "If you spend more on coffee than on IT security, then you will be hacked. What's more, you deserve to be hacked." -Richard Clarke

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Display Main Report if Subreport Has Data (2007 SP1)

    I attach a revised version.

    Instead of a single query to do the work I have broken it into two stages.

    qryPreviousYearsPayments just adds up the Payments per person in previous years.

    The query Prev Cont joins qryPreviousYearsPayments to the table Names (with No Grouping) but uses a Left Join so the names appear even if there are no previous contributions, then the NZ function is used to convert the Null Payment to Zero.
    Attached Files Attached Files
    Regards
    John



  5. #5
    4 Star Lounger
    Join Date
    Mar 2001
    Location
    Bismarck, ND, USA
    Posts
    451
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Display Main Report if Subreport Has Data (2007 SP1)

    This works really well, and takes into account that there may be new names as years go by, ensuring that the new names get a statement, too.

    Can you explain the nz function?
    <hr>PreviousTotal: CCur(nz([SumOfPMT],0))<hr>
    I'm not familiar with the CCur.

    Thanks,
    egghead

  6. #6
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Display Main Report if Subreport Has Data (2007 SP1)

    The NZ Function converts Null to Zero.

    In the query. people who have no previous year donations will not have anything matching records in qryPreviousYearsPayments, so the field SumofPMT would be empty (NULL)
    The NZ function converts that Null to Zero, the the CCur function (Convert to Currency) converts the 0 to $0.00.

    Here is a picture of the query output, with two copies of the SumofPMT field. the first copy does not have the NZ,and CCur functions, the second copy does.
    Attached Images Attached Images
    Regards
    John



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •