Summing a complex formula

  • Thread starter Thread starter JWCrosby
  • Start date Start date
J

JWCrosby

I have a form set to continuous and one of the fields has this as the control
source:
=IIf([EI_Exempt]=Yes,Round(([EIBase]*DFirst("EI_Rate","tblTaxRates")))*Round(DFirst("EI_Employer_Rate","tblTaxRates")),0)

(Round is a defined function...rounds to the nearest cent.)

I've named that field EI_Employee

In the form footer I would like to show the sum of the column. If that's
possible, how would I do it?

I've tried using =SUM([EI_Employee]) and
=SUM(IIf([EI_Exempt]=Yes,Round(([EIBase]*DFirst("EI_Rate","tblTaxRates")))*Round(DFirst("EI_Employer_Rate","tblTaxRates")),0)) but neither has worked.

Any help would be appreciated.
 
I have a form set to continuous and one of the fields has this as the control
source:
=IIf([EI_Exempt]=Yes,Round(([EIBase]*DFirst("EI_Rate","tblTaxRates")))*Round(DFirst("EI_Employer_Rate","tblTaxRates")),0)

(Round is a defined function...rounds to the nearest cent.)

I've named that field EI_Employee

In the form footer I would like to show the sum of the column. If that's
possible, how would I do it?

I've tried using =SUM([EI_Employee]) and
=SUM(IIf([EI_Exempt]=Yes,Round(([EIBase]*DFirst("EI_Rate","tblTaxRates")))*Round(DFirst("EI_Employer_Rate","tblTaxRates")),0)) but neither has worked.

Any help would be appreciated.
Click to expand...

A Sum expression in a form footer can sum a *field in the form's recordsource*
- it will NOT sum a *control on the form*.

I'd suggest putting your first expression into the Query upon which the form
is based as a calculated field EI_Employee, and using that field as the
control source of the textbox on the form, and Sum([EI_Employee]) on the
footer.
 
Back
Top