Sum of a calculated field in a form footer

[Guest]

I have a calculated control in the detail section of a form with the
following formula:
=IIf([CPPFlag]=0,0,TaxSupport(DFirst("CPP_Rate","tblTaxRates"),[CPPBase]))
Key:
CPPFlag is a Yes/No checkbox in the detail section
TaxSupport is a Public Function defined in a Module
the tblTaxRates table contains only 1 record
CPPBase is a calculated control in the detail section

This calculated control in the detail section is working properly.

In the footer of the form, I want to show the sum of the above caclulated
field(s). If I use the following formula I get an Error# message
=Sum(IIf([CPPFlag]=0,0,TaxSupport(DFirst("CPP_Rate","tblTaxRates"),[CPPBase])))

Obviously something about the calculated field is causing a problem but I’m
not knowledgeable enough to see it. Can someone help?

Thanks in advance.

Jerry

 

[Ken Snell \(MVP\)]

Sum requires use of just the fields from the form's Recordsource; thus, you
cannot use CPPBase in the Sum expression. You'll need to replace it with the
actual ControlSource expression that is used for CPPBase control.

=Sum(IIf([CPPFlag]=0,0,TaxSupport(DFirst("CPP_Rate","tblTaxRates"),(TheEntireControlSourceExpressionForThe_CPPBase_Control))))

 

[Guest]

Thanks, Ken. You gave me what I needed to know.

Jerry

Sum requires use of just the fields from the form's Recordsource; thus, you
cannot use CPPBase in the Sum expression. You'll need to replace it with the
actual ControlSource expression that is used for CPPBase control.

=Sum(IIf([CPPFlag]=0,0,TaxSupport(DFirst("CPP_Rate","tblTaxRates"),(TheEntireControlSourceExpressionForThe_CPPBase_Control))))


--

Ken Snell
<MS ACCESS MVP>

I have a calculated control in the detail section of a form with the
following formula:
=IIf([CPPFlag]=0,0,TaxSupport(DFirst("CPP_Rate","tblTaxRates"),[CPPBase]))
Key:
CPPFlag is a Yes/No checkbox in the detail section
TaxSupport is a Public Function defined in a Module
the tblTaxRates table contains only 1 record
CPPBase is a calculated control in the detail section

This calculated control in the detail section is working properly.

In the footer of the form, I want to show the sum of the above caclulated
field(s). If I use the following formula I get an Error# message:
=Sum(IIf([CPPFlag]=0,0,TaxSupport(DFirst("CPP_Rate","tblTaxRates"),[CPPBase])))

Obviously something about the calculated field is causing a problem but I'm
not knowledgeable enough to see it. Can someone help?

Thanks in advance.

Jerry