Summing a calculated field

[Guest]

I have a problem I badly need help with. I'm using the Order Entry template
for Access an d have modified it like this. I have an Orders form with an
order details subform. The subform is like this

ProductName UnitPrice CostPrice Quantity LineTotal CostLineTotal

I'm reading CostPrice into this subform from a Products table like so
=[ProductID].column(3)

CostLineTotal is calculated like so
=[Quantity]*[CostPrice]
and this works fine for all calculations for each line

My problem arises when I try to sum up CostLineTotal in the form footer.
I have tried
=Sum([CostLineTotal])
=Sum([Quantity]*[CostPrice])
and all the variations with nz and CLng

Now, =Sum([Quantity]) in the footer sums up fine but =Sum([CostPrice])
causes #error in all calculations in the form, including unrelated
calculations. When I return to =Sum([Quantity]) in the subform footer, the
calculations return to normal. Any ideas where I'm going wrong? I'm lost

Thanks in advance,
JohnnyF

 

[John Vinson]

I'm reading CostPrice into this subform from a Products table like so
=[ProductID].column(3)

That won't be available in aggregate expressions. You may need to
include the CostPrice in the Query upon which the form is based; you
can Sum() values in the Query, but not the values of (calculated *or*
bound) Controls on the form.

John W. Vinson[MVP]