Toals from subreports

[THWatson]

I have a main report "rptBalanceOwing" and it has 3 subreports "rsubInvoice", "rsubInterest" and "rsubPayments". The main report and the subs are linked by a CustomerID field.

The main report has 4 text boxes. One, Text8, picks up the Invoice totals per customer from "rsubInvoice." A second, Text13, picks up Interest charges from "rsubInterest." The third, Text 21, picks up Payments from "rsubPayments." The 4th text box, Text23, calculates the balance owing (=[Text8]+[Text13]-[Text21])

Each of the first 3 text boxes follows this structure...
=IIf([Invoice].[Report].[HasData]=-1,[rsubInvoice]![Text8],0)

Now, after that explanation, what I want to be able to do is show the grand total of all balances owing. In other words, the total of all balances derived in Text23 for each customer.

Each of the subreports calculates the totals fine, however I can't get the grand totals in the main report to work. It assume it's a syntax problem.

As far as I know, I am properly referring to the subreport controls, e.g. =[rsubInvoice].[Report]![Text8]

Any suggestions would be welcome.
Tom

 

[dhookom]

First of all, I would rename the controls so that they made sense. You might want to try:
What is the name of the subreport control "Invoice" or "rsubInvoice"? You need to use the subreport control name:

=IIf([srptControlName].[Report].[HasData]=-1,[srptControlName].[Report]![txtTotInvoice],0)

You might need to fidgit with the bangs and dots.


Duane
MS Access MVP

 

[THWatson]

Duane
I get your point that the control names don't make sense. But that doesn't solve the problem that I can't produce totals in the report footer. I can get "running sum totals" in the CustomerID footer, but I don't want sums showing up after each customer; I want them only at the end of the report.

With the exception of that, the report works just fine with the control names as they stand. I have checked out the Microsoft Knowledge Base and followed their procedures exactly, as to how to refer to subreport controls in reports.

Anyhow, I will fidgit with the bangs and dots and see if that makes any difference.

There might be one work around. That would be to put some code in the Format event for the CustomerID footer. Do you know how to refer to the last page, or the last customer in the report?

Thanks.
Tom

 

[dhookom]

I think I understand. You have the totals from each subreport displayed properly in the CustomerID footer but want to total for all customers at the end of the report. I think you have two choices:
1) make a running sum of text23 (maybe rename the text box for style points). Then in the report footer, use a text box with a control source of =[text23] or
2) my usual method is to create a totals query that calculates all of this and then either join the totals query into the main report's record source or use another subreport.

Duane
MS Access MVP

 

[THWatson]

Duane
Thanks! Your method (1) works like a charm. I already have a total for each customer in the report (call it [txtBalanceOwing]. So I can add an additional text box that is a running sum of [txtBalanceOwing] and then reference that in the report footer. An additional refinement is to hide that running sum box so that the running sum doesn't show up all through the report.

I knew there had to be a way. I had played with running sums but hadn't got it quite this way.

Tom

 

[dhookom]

Glad it works for you. Good catch on creating the duplicate text box and making one invisible.

Duane
MS Access MVP