Search code examples
ms-accesssubform

Can You Sum Calculated Fields in a Subform?

I am trying to total a subform so I can bring the value up to the main form.

In the subform, the user enters: UnitPrice, which is formatted as a Currency with Decimal Places 0 and Quantity, which is formatted as General Number with Decimal Places 0

The subform calculates Extension = [unitPrice]*[Quantity] in a text box called Extension.

I have created an unbound text box in the subform's footer an inserted the expression =Sum([Extension])

I get #Error when I run the subform and, obviously, an error when I pull the value up to the top level form.

This form contains a combo box for data selection and other form based calculations (not calculated fields in tables...learned my lesson there). These are all working.

When I simply type =[Extension] into the unbound text box in footer, I get the expected value.

When I type =Sum([unitPrice]*[Quantity]) I get the sum of everything in the form's underlying table (expected), but I get the correct value (i.e. only the values related to the top level form) when running the top level form.

Is this the expected behavior or does it indicate a design problem with my data base?

Thanks as always.

Edit. Added screenshot of top level form and subform holding details.

enter image description here

Solution

  • I ended up creating a query - qryItemTotals - to do the required math (Calculate Extension, Ptotal and Factored Value). I then used Dsum in the value and factored value text boxes in the top level form.

    =DSum("Extension","[qryItemTotals]","[opportunityID]=" & [opportunityid])

    and

    =DSum("factoredvalue","[qryItemTotals]","[opportunityID]=" & [opportunityid])

    Not sure this is the best way, but it worked for me.