Add Text Box with Sum Function to Report

[BrianFawcett]

I am trying to add a text box with a SUM function to provide totals at the bottom of a report. The function I am using is below:

=Sum([SummarybyChannelType-LessThan60.SumofAssetCost])

SummarybyChannelType-LessThan60 is a Query
SumofAssetCost is a field derived in that query

When I added it to my report a view it, the text box displays "#ERROR"

Can someone help me out with why this is?

 

[Beetle]

First, where ae you placing the text box? It needs to go in the Report footer, not the Page footer.

Second, if [SummarybyChannelType-LessThan60] is the record source of the report then;

=Sum([SumOfAssetCost])

should work.

However, if you are trying to retrieve a value from a query that is not the record source of the report then you will need to do one of the following;

1) If possible, join [SummarybyChannelType-LessThan60] to the existing record source of the report so you can get the SumOfAssetCost field in the record set.

2) Use DSum to retrieve the value;

=DSum("SumOfAssetCost", "[SummarybyChannelType-LessThan60]")

In this scenario you may also need to add criteria to the DSum to get the correct value, depening upon your particular circumstances.

 

[BrianFawcett]

Beetle, Thanks for the post. I was able to do it in the report footer using the Report Layout Sum tool.​