Sum of a calculated field in a form footer

J

JWCrosby

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
 
K

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))))
 
J

JWCrosby

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

Jerry

Ken Snell (MVP) said:
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>

JWCrosby said:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top