Search code examples
ms-accessaveragecalculated-fieldsubform

Average of calculated field in Access subform

I am working in Access 2007 with a subform that shows the Mass of a determined set of records linked to a master form. The form is displayed in spreadsheet view and has two columns [Mass] and [Include]. Include is a True/False field to select whether the record is to be included in the final calculation.

In the footer of the subform, I want to add the average of the masses of the records where [Include] = True. So far I have created a new field [MassIF] = IIf([Include]=True,[Mass],Null), this works correctly in the spreadsheet.

However, the =Avg([MassIF]) control in the footer gives an error, any idea why that would be and how to get around the issue?

Also, =Avg([Mass]) works fine until I add to the footer the =Avg([MassIF]) control, then I get #ERROR in both fields, which looks really odd to me.

Solution

  • Try calculation in textbox in footer:

    =Avg(IIf([Include], [Mass], Null))