# Cannot Sum a Calculated Control in the Report Footer (1 Viewer)

#### Tophan

##### Registered User.
Good Afternoon,

I am trying to sum a calculated control in a report footer but not having any luck.

This is a quarterly report that calculates the insurance payable per month within the quarter based on a set percentage per invoice. Note, the insurance payable is not calculated per invoice but per total invoices per month.

E.g.

 Date Invoice # Total Insurable Amount (70% of Total on totals >\$50) Insurance (17.1% of the Total Insurable Amount for the Month Up to but not exceeding \$5200) 23-Jan-2024 01 6000.00 4200.00 31-Jan-2024 02 3000.00 2100.00 31-Jan-2024 03 187.50 131.25 31-Jan-2024 04 500.00 350.00 31-Jan-2024 05 20.00 0.00 6781.25 889.20

Up to Insurable Amount is calculated in the associated query. The total insurable is summed in the report in a text box named txtTTLInsurable in the Month group footer.

The Insurance per month is calculated in the Report in the Month group footer and the formula for that is
Code:
``=IIf([txtTTLInsurable]>=5200,5200*0.171,[txtTTLInsurable]*0.171)``
.

I would like to show a grand total for the quarter in the report footer showing the total Insurance payable but I can't seem to figure out the formula.

I've tried
Code:
``=Sum(IIf([txtTTLInsurable]>=5200,5200*0.171,[txtTTLInsurable]*0.171))``
but no luck.

Any assistance would be appreciated.

#### theDBguy

##### I’m here to help
Staff member
What were you getting with that expression?

#### Tophan

##### Registered User.
When I try to run the report with the expression to sum the insurance, a box pops up asking to Enter the Parameter Values for the text box txtTTLInsurable. That is the text box in the Month group footer.

#### Tophan

##### Registered User.
So I found a work around which is giving me the correct total, I'm just wondering if it's the best solution.

In the report footer I calculated the grand total for the total insurable and named the text box GTInsurable. Then to calculate the total insurance payable per quarter I created a text box and entered the following formula

Code:
``=IIf([GTInsurable]>=15600,15600*0.171,[GTInsurable]*0.171)``

The end result is correct just not sure if the method is the best one.

Let me know what you think.

#### The_Doc_Man

##### Immoderate Moderator
Staff member
Just FYI, when you see this behavior:

a box pops up asking to Enter the Parameter Values for the text box txtTTLInsurable

that means it can't SEE [txtTTLInsurable] (because it either doesn't exist at all or the report doesn't know where to find it.) If it exists and you want to use it, it must be properly qualified to tell Access where it is/how to find it.

When you tried to use your computation for txtTTLInsurable, was it used in the same footer in which it was computed or is that quarterly total in a different footer? Because that might be part of the problem.

#### June7

##### AWF VIP
Aggregate functions must reference fields from RecordSource, not controls. So, the solution you used is likely the best.

#### Tophan

##### Registered User.
Aggregate functions must reference fields from RecordSource, not controls. So, the solution you used is likely the best.
Thank you

#### Tophan

##### Registered User.
Just FYI, when you see this behavior:

that means it can't SEE [txtTTLInsurable] (because it either doesn't exist at all or the report doesn't know where to find it.) If it exists and you want to use it, it must be properly qualified to tell Access where it is/how to find it.

When you tried to use your computation for txtTTLInsurable, was it used in the same footer in which it was computed or is that quarterly total in a different footer? Because that might be part of the problem.
Hello,

So the [txtTTLInsurable] is in the Month group footer. I also used the same formula in the Report footer. What I don't have is any sub-totals in the Quarter group footer. As I run the report for one quarter at a time, I didn't use the Quarter group footer, only the Month group footer showing the sub-totals per month within the quarter.

#### The_Doc_Man

##### Immoderate Moderator
Staff member
Since the report is generally done one section at a time and the footers (of various types) are just sections, things computed in the footer and not otherwise stored somewhere (perhaps in the report's general declaration area) are not available outside of their section. Which is what June7 was pointing out. What you compute in a section is only available in that section. Forms rarely have this problem because they only have one section called Detail. Reports have MANY sections, each one with a (temporary) mind of its own.

#### June7

##### AWF VIP
Doc, don't think that's what I was pointing out at all.

I state again, aggregate functions cannot refer to controls, only fields.

Otherwise, a control in report/group header/footer section certainly can be referred to in a group or detail section. This is how percentages can be calculated as well as a running balance with a starting balance. Calc in group or report footer textbox =Sum(Qty*Price) then in detail section refer to the Sum textbox to get percentage: =Qty*Price/tbxSum

Last edited:

#### Pat Hartman

##### Super Moderator
Staff member
You cannot use calculated controls in other calculations in a different section. You must repeat the calculation.

ControlA - controlsource
=fld1 + fld2

ControlB - controlsource - WRONG
=Sum(ControlA)

ControlB - controlsource - CORRECT -- you repeat the calculation
=Sum(fld1 + fld2)

#### The_Doc_Man

##### Immoderate Moderator
Staff member
Pat's statement is perhaps more direct than mine, but expresses what I was saying.

Replies
6
Views
719
Replies
6
Views
439
Replies
3
Views
560
Replies
2
Views
301
Replies
7
Views
728