avg.

[gdzilla]

i have an expression that calculates the average of two pieces of data: =Sum([qryStatistics]![IspReqNot])/Sum([qryStatistics]![IspReq])

the problem is the values that are being divided is "0": 0/0
so the percentage is 0 which is returning an error.

how can make it output a 0%?

thanks in advance!

 

[grnzbra]

IIf(denominator = 0,0,your expression)

 

[gdzilla]

hey grnzbr...

thanks for the response. i change my expression to the following from what you said:

=IIf([qryStatistics]![OneReq]=0,0,Sum([qryStatistics]![TwoReqNot])/Sum([qryStatistics]![TwoReq]))

when i tried to run it in my report it asks for the value of "qryStatistics" when it already has a 0 value?

 

[grnzbra]

Your denominator is not qryStatistics!TwoReg. It's Sum(qryStatistics!TowReg) I don't know if that's causing your error message, but if you have two values prior to the sum, +5 and -5, the sum will be zero but your IIf statement won't see it.

does it have zero or is it null? perhaps that's causing the message.

 

[gdzilla]

it has a zero.

i changed the parameter for the denominator to the correct one:

=IIf(Sum([qryStatistics]![TwoReq])=0,0,Sum([qryStatistics]![TwoReqNot])/Sum([qryStatistics]![TwoReq]))

it's no longer giving me errors, but it's output is 100% when it should be a 0%?

 

[grnzbra]

Sum([qryStatistics]![TwoReq])= 0 and the IIf isn't slamming it to zero?

 

[gdzilla]

hey grnzbra..

i'm sorry, but i don't understand what you said. please explain.

thanks!

 

[grnzbra]

If the first part of the IIf statement is true, the value goes to whatever is behind the first comma, otherwise, it uses whatever is behind the second comma. I am just checking to see if I understand what's happening. The first part of the statement is true but it is performing the calculation rather than just going to the value (0) following the first comma. Is this what is happening?

 

[gdzilla]

yes, that is what's happening. the sum of TwoReq is zero, but it's outputing a '1' instead of grabbing the zero.

i'm not sure where it's even getting the '1' since when the calculation is done the answer should still be zero.

 

[gdzilla]

yes, that is what's happening. the sum of TwoReq is zero, but it's outputing a '1' instead of grabbing the zero.

i'm not sure where it's even getting the '1' since when the calculation is done the answer should still be zero.

 

[grnzbra]

I had a problem years ago with Access 3.5 where there was something in the 25th or so decimal place and testing for zero came up false. Try setting the first condition to between -.0000001 and .0000001. This is grasping at straws but...

 

[gdzilla]

grnzbra..

thanks for your all your help, but i'm still getting a one as the output.

 

[grnzbra]

Try doing it in a new data base with a small table (2 or 3 records and see what you get.

 

[grnzbra]

The two pieces of data mean what? You have IspReqNot and IspReq. Are these numbers of ISP's required and not required for that record or are they boolean (yes it's requiried or no it's not required for this record.) If it's boolean, perhaps you need a count function rather than a sum function

 

[gdzilla]

grnzebra...

i just figured out why i am getting a one instead of a zero. i was grabbing the wrong value. i needed to use the value from "One" instead of "Two". sorry for putting you through that. this formula works:

=IIf(Sum([qryStatistics]![OneReq])=0,0,Sum([qryStatistics]![OneReqNot])/Sum([qryStatistics]![OneReq]))

thanks for your help!