Search code examples
ms-accessms-access-2007aggregate-functionssubform

Use subform record set as domain argument in DAvg()

Is it possible to use a subform's 'current' record set as the domain argument to DAvg() (etc.)?

Basically, I have a subform that displays a subset of records from a query. I would like to run DAvg() over this subset. This is how I've gotten around it:

=DAvg([FieldToAvg], [SubformQuery], "ChildField=Forms.MasterForm.MasterField And FieldToAvg > 0")

but what I actually want is something like:

=DAvg([FieldToAvg], [SubformCurrentlyDisplayedData], "FieldToAvg > 0")

Is this possible in Access 2007?

Solution

  • Is there any reason why you do not want to use a subform footer, hidden or otherwise, to do this? It is the simplest way.

    In a textbox:

    =Avg([SomeField])