|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
December 10th, 2009, 08:55 PM
posted to microsoft.public.access.queries
|
|||
|
|||
Divide by Zero error
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 
|
|
#2
December 10th, 2009, 09:03 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Divide by Zero error
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" wrote: 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
|
|
#3
December 10th, 2009, 09:29 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Divide by Zero error
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 Jerry Whittle wrote: 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.
|
|
#4
December 10th, 2009, 09:45 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
Divide by Zero error
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" wrote: 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" wrote: 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
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
