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

Tophan

Registered User.
Local time
Today, 04:32
Joined
Mar 27, 2011
Messages
367
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.

DateInvoice #TotalInsurable 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-2024016000.004200.00
31-Jan-2024023000.002100.00
31-Jan-202403187.50131.25
31-Jan-202404500.00350.00
31-Jan-20240520.000.00
6781.25889.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
Local time
Today, 01:32
Joined
Oct 29, 2018
Messages
21,613
What were you getting with that expression?
 

Tophan

Registered User.
Local time
Today, 04:32
Joined
Mar 27, 2011
Messages
367
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.
Local time
Today, 04:32
Joined
Mar 27, 2011
Messages
367
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
Local time
Today, 03:32
Joined
Feb 28, 2001
Messages
27,488
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
Local time
Today, 00:32
Joined
Mar 9, 2014
Messages
5,503
Aggregate functions must reference fields from RecordSource, not controls. So, the solution you used is likely the best.
 

Tophan

Registered User.
Local time
Today, 04:32
Joined
Mar 27, 2011
Messages
367
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
Local time
Today, 03:32
Joined
Feb 28, 2001
Messages
27,488
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
Local time
Today, 00:32
Joined
Mar 9, 2014
Messages
5,503
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
Local time
Today, 04:32
Joined
Feb 19, 2002
Messages
43,720
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
Local time
Today, 03:32
Joined
Feb 28, 2001
Messages
27,488
Pat's statement is perhaps more direct than mine, but expresses what I was saying.
 

Users who are viewing this thread

Top Bottom