Calucating Rpt/SubRpt

[Guest]

I have a report and an subrpt and would like to calculate a total on the
report using a field from the report and a field from the subreport. Below
is the calculation and whenever I try to run the report, I get a "Enter
Parameter Value". I've tried also pulling the "idamt" from the subreport as
well as from the query. I have had this problem before on other reports and
subreports and can never figure out what I'm doing wrong.

=Sum([amt]+[QRYsubrptInvoice]![idamt])

Any help would be appreciated.

 

[Marshall Barton]

I have a report and an subrpt and would like to calculate a total on the
report using a field from the report and a field from the subreport. Below
is the calculation and whenever I try to run the report, I get a "Enter
Parameter Value". I've tried also pulling the "idamt" from the subreport as
well as from the query. I have had this problem before on other reports and
subreports and can never figure out what I'm doing wrong.

=Sum([amt]+[QRYsubrptInvoice]![idamt])

The aggregate functions (Count, Sum, etc) only operate on
fields in the report's record source table/query, they are
unaware of controls on the report and are certainly unable
to dig down into a subreport.

Your reference to the subreport value is also incomplete.
If the subreport is in the main report's header or footer
section, then you want to use:
=Sum(amt) + QRYsubrptInvoice.REPORT!idamt

If the subreport is in the main report's detail section,
then you need to calculate the total from all instances of
the subreport. This is done by adding a text box named
txtRunAmt to the detail section. Set its control source
expression to:

=IIf(QRYsubrptInvoice.REPORT!HasData,QRYsubrptInvoice.REPORT!idamt,0)
and set its RunningSum property to Over All.

Then the grand total in the main report's footer would use
the expression:
=Sum(amt) + txtRunAmt

 

[Guest]

Perfect ... thanks so much for your help on a Sunday morning!

I have a report and an subrpt and would like to calculate a total on the
report using a field from the report and a field from the subreport. Below
is the calculation and whenever I try to run the report, I get a "Enter
Parameter Value". I've tried also pulling the "idamt" from the subreport as
well as from the query. I have had this problem before on other reports and
subreports and can never figure out what I'm doing wrong.

=Sum([amt]+[QRYsubrptInvoice]![idamt])

The aggregate functions (Count, Sum, etc) only operate on
fields in the report's record source table/query, they are
unaware of controls on the report and are certainly unable
to dig down into a subreport.

Your reference to the subreport value is also incomplete.
If the subreport is in the main report's header or footer
section, then you want to use:
=Sum(amt) + QRYsubrptInvoice.REPORT!idamt

If the subreport is in the main report's detail section,
then you need to calculate the total from all instances of
the subreport. This is done by adding a text box named
txtRunAmt to the detail section. Set its control source
expression to:

=IIf(QRYsubrptInvoice.REPORT!HasData,QRYsubrptInvoice.REPORT!idamt,0)
and set its RunningSum property to Over All.

Then the grand total in the main report's footer would use
the expression:
=Sum(amt) + txtRunAmt