Cannot Sum a Calculated Control in the Report Footer

[Tophan]

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

=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

=Sum(IIf([txtTTLInsurable]>=5200,5200*0.171,[txtTTLInsurable]*0.171))

but no luck.

Any assistance would be appreciated.

 

[theDBguy]

What were you getting with that expression?

 

[Tophan]

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]

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

=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]

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]

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

 

[Tophan]

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

Thank you

 

[Tophan]

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]

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]

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

 

[Pat Hartman]

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]

Pat's statement is perhaps more direct than mine, but expresses what I was saying.