Hide SubReports with no data BUT still include them in Grand Total calculation

[James]

I have read with interest much of the discussion regarding subReports and
Totals etc.

I understand how to not print a subreport on a report when the subreport has
no data but when I try to calculate the Grand Total of all subReports at the
bottom of the main report I get an error whenever one of the subReports
total is zero.

I can force the subReport to display a zero if it has a Null value (Thanks
to some informative posts here) but then it will print on the main report.

See the problem?

 

[Marshall Barton]

I have read with interest much of the discussion regarding subReports and
Totals etc.

I understand how to not print a subreport on a report when the subreport has
no data but when I try to calculate the Grand Total of all subReports at the
bottom of the main report I get an error whenever one of the subReports
total is zero.

I can force the subReport to display a zero if it has a Null value (Thanks
to some informative posts here) but then it will print on the main report.

See the problem?

No, I'm afraid I don't see it.

A subreport with no data will not display anything. So, if
you're getting it to display, you must either be faking a
data record or displaying some phony image that is not the
subreport.

If the problem is in summing the values, it would be a big
help if you explained how you were attempting to do it.
The standard way to do it is to use a running sum text box
with an expression like:

=IIf(subreport.Report.HasData, subreport.Report.subtotal, 0)

But, if you're having trouble, you are probably doing
something(?) else.

 

[James]

Ok. I'll do my best to explain the situation.

I have a report with about 5 subreports.

Each subreport includes any number of items with prices which total at the
bottom of the subreport. (except of course where there is no data in the
subreport)

What I want to happen is for the Grand Total of all the subreports to appear
at the bottom of the main report. (Something which should be simple!)

I have found that if there is no data in any one of the subreports then the
"Grand Total" calculation produces an error.

The "Grand Total" is derived from a query which looks like this:

SELECT tbl_Quotes.QuoteID,
Sum([PaintGTotal]+[PartsGTotal]+[RepairGTotal]+[RrGTotal]+[SubLet
Total]+[InstallGTotal]) AS GrandTotal
FROM (((((tbl_Quotes INNER JOIN sqrySublet ON tbl_Quotes.QuoteID =
sqrySublet.QuoteID) INNER JOIN sqryRrGTotal ON tbl_Quotes.QuoteID =
sqryRrGTotal.QuoteID) INNER JOIN sqryRepairGTotal ON tbl_Quotes.QuoteID =
sqryRepairGTotal.QuoteID) INNER JOIN sqryPartsGTotal ON tbl_Quotes.QuoteID =
sqryPartsGTotal.QuoteID) INNER JOIN sqryPaintGTotal ON tbl_Quotes.QuoteID =
sqryPaintGTotal.QuoteID) INNER JOIN sqryInstallGTotal ON tbl_Quotes.QuoteID
= sqryInstallGTotal.QuoteID
GROUP BY tbl_Quotes.QuoteID;

So I took the advice of this group and included a

=IIf([subreportnamehere
Total].Report.HasData,NZ(subreportnamehere.Report.Text1,0),0)

field on each subreport so the value of subreportnamehere total would
display as a zero. This seemed to have fixed the problem with the "Grand
Total" calculation.

HOWEVER, then the subreport with no data, except for the zero total, would
display in the final report. Something I do not want.

Surely it should be a relatively simple matter to total the totals of each
subreport at the end of the main report but not print any subreports that
have no data?!!!!

Thanks,