Avoid #Error Value In Query

[Guest]

I Got a Query that is Calculating the True Average of the 5 Days of the Week by Product.
What happen is, some products can be 0 at the end of the week(Product not Produced), but i stiil have to put a 0 in the Field
Simple Question:
The Problem is if all Values of the week r 0, it Wiil Return #Error

i Using:
Avg Of Rump: IIf(IsError(Sum([Rump])/Sum(Abs([Rump]>0)))=True,0,Sum([Rump])/Sum(Abs([Rump]>0)))

But the sintax is wrong(i Think!!!)
i haved try This as Well:
Avg Of Rump: IIf((Sum([Rump])/Sum(Abs([Rump]>0)))="#Error",0,Sum([Rump])/Sum(Abs([Rump]>0)))

I Open to New Ideias
Thank u..

 

[Allen Browne]

You could use IsError(), rather than comparing the result to the text
"#Error".

However, the source of the problem is that you cannot divide by zero:
IIF(Sum(Abs([Rump]))=0, 0, ... calculation here)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I Got a Query that is Calculating the True Average of the 5 Days of the Week by Product.
What happen is, some products can be 0 at the end of the week(Product not

Produced), but i stiil have to put a 0 in the Field

Simple Question:
The Problem is if all Values of the week r 0, it Wiil Return #Error

i Using:
Avg Of Rump: IIf(IsError(Sum([Rump])/Sum(Abs([Rump]>0)))=True,0,Sum([Rump])/Sum(Abs([Rump
]>0)))

But the sintax is wrong(i Think!!!)
i haved try This as Well:
Avg Of Rump: IIf((Sum([Rump])/Sum(Abs([Rump]>0)))="#Error",0,Sum([Rump])/Sum(Abs([Rump]>0
)))

I Open to New Ideias
Thank u..