Avoid #Error Value In Query

  • Thread starter Thread starter Guest
  • Start date Start date
G

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..
 
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.

Roy said:
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
Click to expand...
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..
Click to expand...
 
Back
Top