Is there a simpler way to have a sum field in a footer?

[Guest]

In my form, one of the controls in the detail section is a calculated control
with the following formula:

=[TaxableWage]-[MinistryExpense]-[CPPContribution]

I want to have the sum of that field in the form footer. My understanding
is that if any formulas are used in the original source (i.e., what I show
above), those formulas must be spelled out in the Sum calculation.

The field above DOES have calculations within it as follows:

TaxableWage: ([TempNet]-[BC_Tax])

TempNet: Net([GrossAvailable],[Operations]) (A function in a module)

BC_Tax:
IIf([BCFlag]=0,0,DLookUp("[BC_WCB_Rate]","tblTaxRates")*Net([GrossAvailable],[Operations]))

CPPContribution:
IIf([CPPFlag]=0,0,TaxSupport(DFirst("CPP_Rate","tblTaxRates"),[CPPBase]))

My question is this: is there an easier way to show the sum in the footer
besides compiling a very complex Sum calculation using all the formulas
above? It never fails that I end up leaving out a paranthesis (or 2 or 3!!)
and trying to troubleshoot a VERY LONG calculation is tedious at best.

Maybe someone knows a short-cut.

Thanks in advance.

Jerry

 

[Guest]

Hi JW,

Have you thought about moving all of the calculations to the query?

Then the controls on the form can be bound to a field. In the footer you can
use

=Sum(NetIncome) ' (just an example)


I have many complex date sensetive calculations that are function calls in
my queries. For some queries that return ~2000 records, it does take about 30
secs., but it is faster (in my limited testing) than doing the calculations
on the form or report.

It also gives me the ability to run the query without opening the form, to
see intermediate calculations by adding temp columns (if necessary).

You can use the query as the source "table" for other queries for other
forms/reports where you can limit (filter) the records to a smaller subset
without creating lots of queries to do the same thing..

OK, that is my $0.02 worth...

HTH

 

[Guest]

Thanks, Steve. I may go back to the drawing board and try your suggestion.

Jerry

Hi JW,

Have you thought about moving all of the calculations to the query?

Then the controls on the form can be bound to a field. In the footer you can
use

=Sum(NetIncome) ' (just an example)


I have many complex date sensetive calculations that are function calls in
my queries. For some queries that return ~2000 records, it does take about 30
secs., but it is faster (in my limited testing) than doing the calculations
on the form or report.

It also gives me the ability to run the query without opening the form, to
see intermediate calculations by adding temp columns (if necessary).

You can use the query as the source "table" for other queries for other
forms/reports where you can limit (filter) the records to a smaller subset
without creating lots of queries to do the same thing..

OK, that is my $0.02 worth...

HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)

In my form, one of the controls in the detail section is a calculated control
with the following formula:

=[TaxableWage]-[MinistryExpense]-[CPPContribution]

I want to have the sum of that field in the form footer. My understanding
is that if any formulas are used in the original source (i.e., what I show
above), those formulas must be spelled out in the Sum calculation.

The field above DOES have calculations within it as follows:

TaxableWage: ([TempNet]-[BC_Tax])

TempNet: Net([GrossAvailable],[Operations]) (A function in a module)

BC_Tax:
IIf([BCFlag]=0,0,DLookUp("[BC_WCB_Rate]","tblTaxRates")*Net([GrossAvailable],[Operations]))

CPPContribution:
IIf([CPPFlag]=0,0,TaxSupport(DFirst("CPP_Rate","tblTaxRates"),[CPPBase]))

My question is this: is there an easier way to show the sum in the footer
besides compiling a very complex Sum calculation using all the formulas
above? It never fails that I end up leaving out a paranthesis (or 2 or 3!!)
and trying to troubleshoot a VERY LONG calculation is tedious at best.

Maybe someone knows a short-cut.

Thanks in advance.

Jerry