getting sum of calculated field in subform footer

[Cowboy_BeBa]

hi everyone

ive got a subform (simple table view) inside a main form, the subform (sfrmRecipes) just has an IngID field (which stores ingredient ID's) and a percentage field

ive added a few calculated fields to this subform, to assist users in making decisions, however each of these fields needs a sum total

so far my percentage field (which is an actual stored field in tblRecipes) is the only one with a functioning sum
for some reason the two calculated fields (which are both plain numbers, no text) do not give me a sum total option, i am only able to use the count function on them

does anyone know why calculated fields dont sum up and if there is an easy way to get it working (tried creating my own text field in the footer and manually typing SUM(fieldname), but it does not work)

 

[burrina]

Are the calculated fields in your query? If so, refer to the field and set its control source to it. Else, here is an example:

=Nz(Sum([GrandTotal]),0)

 

[AlexN]

Like burrina says, the calculated fields have to be in a query, otherwise they are not really fields of the form and you can’t use them in a function. So there are two ways of accomplishing the task :

• 1. First Way: You create a query which performs the calculations and you bound the calculation fields of your form accordingly. Then you should be able to SUM them in your subform footer.

• 2. Second Way: Suppose your subform has the fields [A] and and the calculated field [C] = [A]*. To get the SUM of [C]s in the footer, you have to create a textbox with the datasource SUM([A]*),instead of the SUM([C])
  

Of course the use of the Nz expression is always appreciated.