Divide by Zero error

  • Thread starter Thread starter Jecker
  • Start date Start date
J

Jecker

I need assistance please. I have 2 fields called PWP_TRX and TOTAL_TRX. I
have a query expression that reads sum([PWP_TRX])/sum([TOTAL_TRX). On
occasion both these fields will be zero. My formula above is reflecting
#error when it comes across the zero fields. I know I need to use an IIF but
I can't remember - it's been a long time. Any assistance will be greatly
appreciated. Thank you
 
sum([PWP_TRX])/NZ(sum([TOTAL_TRX),1)

A lot depends on what you want returned if either, or both, are 0. The above
will divide by 1.
 
IIF(SUM(Total_Trx)<>0,sum([PWP_TRX])/sum([TOTAL_TRX),Null)

Replace Null with zero if you want that result returned.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Actually I think this might be the solution:
IIf(sum([TOTAL_TRX) = 0 , 0 ,sum([PWP_TRX])/sum([TOTAL_TRX))

--
Duane Hookom
Microsoft Access MVP


Jerry Whittle said:
sum([PWP_TRX])/NZ(sum([TOTAL_TRX),1)

A lot depends on what you want returned if either, or both, are 0. The above
will divide by 1.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Jecker said:
I need assistance please. I have 2 fields called PWP_TRX and TOTAL_TRX. I
have a query expression that reads sum([PWP_TRX])/sum([TOTAL_TRX). On
occasion both these fields will be zero. My formula above is reflecting
#error when it comes across the zero fields. I know I need to use an IIF but
I can't remember - it's been a long time. Any assistance will be greatly
appreciated. Thank you
Click to expand...
Click to expand...
 
Back
Top