Summing in a subform(again)

[Guest]

I thought I had this problem licked but I don't now. I have a subform with
the following

ProductName UnitPrice CostPrice Quantity UnitSubtotal CostSubtotal

where UnitSubtotal is =[UnitPrice]*[Quantity]
CostSubtotal is =[CostPrice]*[Quantity]

In the footer I want to sum up all the values in both subtotal lines. I have
tried
UnitTotal =Sum([UnitPrice]*[Quantity])
CostTotal =Sum([CostPrice]*[Quantity])
but I get #Error coming up for some reason. When I put in nz(Sum..) the
#Error goes away but I get both values to be 0. Any ideas?

Thanks in advance,
JohnnyF

 

[Guest]

Hi Jonny

The equation you have use is fine “providing†the every record has a
numerical value above 0. ie this would work fine

=Sum( Nz ( [UnitPrice, 0) ) * Nz ( [Quantity] , 0)

The problem with this is that is “any†of your amounts are 0 then the answer
will be 0 as any number multiplied by 0 = 0

I would suggest that you 1st total and use Nz the fields and then simply
multiply the totals. This will work if any of your field have any amount
above 0 (which I assume they do)
=Sum( Nz ( [UnitPrice, 0) I have called this FieldNameA
=Sum( Nz ( [Quantity, 0) I have called this FieldNameB

Then use the totals in a new text box
=sum([FieldNameA] * [FieldNameB])

Hope this helps

 

[Guest]

Thanks for your help,
Unfortunately, it doesn't seem to work. When I try and read in the value
of Sum([UnitPrice]*[Quantity]) from the subform nothing appears.
if I change it just to Sum([Quantity]) on its own it reads in fine but it
won't read in for Sum([UnitPrice]) so therein lies my problem.

Within the subform, UnitPrice =[cboProductID].column(2) where cboProductID
is a combo box that looks up values from a Products query with
ProdID ProdName UnitPrice CostPrice

Is this where I'm going wrong? Thanks again

Johnny

Hi Jonny

The equation you have use is fine “providing†the every record has a
numerical value above 0. ie this would work fine

=Sum( Nz ( [UnitPrice, 0) ) * Nz ( [Quantity] , 0)

The problem with this is that is “any†of your amounts are 0 then the answer
will be 0 as any number multiplied by 0 = 0

I would suggest that you 1st total and use Nz the fields and then simply
multiply the totals. This will work if any of your field have any amount
above 0 (which I assume they do)
=Sum( Nz ( [UnitPrice, 0) I have called this FieldNameA
=Sum( Nz ( [Quantity, 0) I have called this FieldNameB

Then use the totals in a new text box
=sum([FieldNameA] * [FieldNameB])

Hope this helps

--
Wayne
Manchester, England.
Enjoy whatever it is you do

I thought I had this problem licked but I don't now. I have a subform with
the following

ProductName UnitPrice CostPrice Quantity UnitSubtotal CostSubtotal

where UnitSubtotal is =[UnitPrice]*[Quantity]
CostSubtotal is =[CostPrice]*[Quantity]

In the footer I want to sum up all the values in both subtotal lines. I have
tried
UnitTotal =Sum([UnitPrice]*[Quantity])
CostTotal =Sum([CostPrice]*[Quantity])
but I get #Error coming up for some reason. When I put in nz(Sum..) the
#Error goes away but I get both values to be 0. Any ideas?

Thanks in advance,
JohnnyF