How to use Sum() in my case ?

[Albert]

Hello, I create a continuous form. Each record display an SpecialAmount
field which its ControlSource is "=CalAmt([Qty],[Flag])". This function can
work correctly. Then, In the Form's Footer section, I need a summation of
SpecialAmount (Lets say "SumSpecialAmount"). I create it with
"=Sum(CalAmt([Qty],[Flag]))" as ControlSource. But it generate "#Error" in
the field. I have no idea on how to do that. Please help.

Thank you,
Suntisuk

 

[Jonathan Parminter]

-----Original Message-----
Hello, I create a continuous form. Each record display an SpecialAmount
field which its ControlSource is "=CalAmt([Qty],[Flag])". This function can
work correctly. Then, In the Form's Footer section, I need a summation of
SpecialAmount (Lets say "SumSpecialAmount"). I create it with
"=Sum(CalAmt([Qty],[Flag]))" as ControlSource. But it generate "#Error" in
the field. I have no idea on how to do that. Please help.

Thank you,
Suntisuk

Hi Suntisuk,
I usually put calculations such as "=CalAmt([Qty],[Flag])"
in the form's record source query - it can process faster
at the query level than at the form level. The form then
uses the calculated field name as a control source. Then
in the form footer I have a textbox with the control
source "=Sum([My calculated field])".

To create a calculated field in query type into a blank
column
My_calculate_field:CalAmt([Qty],[Flag])

To the left of the colon is the name you want as field
reference (preferably without spaces).
To the right of the colon is the calculation.

Even if you don't have the calculation evaluated in the
source query, you can still use as control source "=sum
([form control name])"

[form_control_name] is the name that is found on
the 'other' tab of the control property dialog.

Luck
Jonathan