Why the Avg difference?

[rschultz]

I have the following formula in a textbox in the page footer of a report:

=Sum(IIf([Prod_List] Like '*Compact',[Weigh_Net]/2000,0)) the result is 694

beneath it I have another textbox with the following formula:

=Avg(IIf([Prod_List] Like '*Compact',[Weigh_Net]/2000,0)) the result is 5.46

I also have a textbox with the following formula:

=Sum(IIf([Prod_List] Like '*Compact',1,0)) which is a counter of the instances of the first textbox above, the result is 101

If I divide the 694 by 101 shouldn't I get the same as the Avg funtion? Instead I get 6.87

Why don't I get the same amount as the Avg funtion? Which one is correct? It wouild seem 694 by 101 woiuld be correct.

 

[rschultz]

sorry, I'm not understanding.

I don't understatnd how to use [Prod_List] like "*Compact" in the textbox on the detail band without the IIf and get the Weight.

Also, I tried =Sum([Weigh_Net]/2000,0) in the textbox in the report daily footer and I got: "The expression you entered has a function containing the wrong number of arguements".

And I tried:
=([Prod_list] like "*Compact" [Weigh_Net]/2000,0) in the textbox in the detail band and I get:
"The expression you entered contains invalid syntax"
So I'm afraid I'm confused.

 

[rschultz]

I think I understood the explaination about why Avg() wasn't working. The Avg funtion wasn't looking at the records selected by the IIf Statement it was looking at the whole recordset.

I think I need to clarify something else though. I was using the IIf because I need to get Like "*Compact" in one total and Like "*Floor" in another. I got it figured out though. Thanks for pointing me in the right direction. I'm sure something else will confuse me later today<grin>.