why am i getting #Num!

[KrisDdb]

Hi All

Can anyone tell me what I need to add/modify to get the below given ratio as a percent This code works fine when I have a proper numerator and denominator. When it comes to 0/0, I am getting the result as #Num! instead of which, it should be displaying 0


=(Count([Field1])-Count(IIf([Field1]="N/A",0)))/((Count([Field1])-Count(IIf([Field1]="N/A",0)))+(Count(IIf([Field2]="Y",0)+IIf([Field1]="N/A",0))))

Appreciate your time and help.

[ssanfu]

Hi All

Can anyone tell me what I need to add/modify to get the below given ratio as a percent This code works fine when I have a proper numerator and denominator. When it comes to 0/0, I am getting the result as #Num! instead of which, it should be displaying 0


=(Count([Field1])-Count(IIf([Field1]="N/A",0)))/((Count([Field1])-Count(IIf([Field1]="N/A",0)))+(Count(IIf([Field2]="Y",0)+IIf([Field1]="N/A",0))))

Appreciate your time and help.

Well, I see a few things wrong. You are getting the error because you cannot divide by zero. If the denominator is 0 (zero), you ge thte divide by zero error. So you need to handle that.

Next, you are missing the FALSE clause in the IIF() functions. the syntax is :

IIF(condition, value if TRUE, value if FALSE)

All you have is the value if TRUE clause. You need to add something for the value if FALSE clause.

[KrisDdb]

Well, I see a few things wrong. You are getting the error because you cannot divide by zero. If the denominator is 0 (zero), you ge thte divide by zero error. So you need to handle that.

Next, you are missing the FALSE clause in the IIF() functions. the syntax is :

IIF(condition, value if TRUE, value if FALSE)

All you have is the value if TRUE clause. You need to add something for the value if FALSE clause.

Hey Steve

Thanks for the quick post.. and really sorry for the late reply!! I got the problem solved by tweaking the code a bit.. so i forgot to check back on the post. So now I have decided to postback so it might help others who get into the same problem. Heres what I did..


For the first wrong that you pointed out - Dividing by zero:

Getting a zero in the denominator cannot be wrong. The situation is such that I get a zero sometimes in the denominator based on some calculations. And when that happens, I wanted the display to be zero instead of the 'divide by zero error'. For this, all I had to do was to use an 'AND' operator in the Expression of the IIF function.

IIf (Numerator = 0 AND Denominator = 0,0, (Numerator/Denominator))

This gave '0' when both num and den were zero. Else it would return the calculated value..
Earlier when I was having only the (Num/Den) in the expression part, it resulted in #num!

And for the second part - Missing the False Clause

Like I already mentioned, the code was working fine before... i.e even without the false clause. Its only when I got a '0' in place of both the num and den that I ran into the #num! display trouble. But the above code solved it.

BTW, am not sure if the false part is mandatory.. the access gurus might be able to shed some light on it. But it has worked for me without it.

But anyways.. thanks for checking into it.

[ssanfu]

For the first wrong that you pointed out - Dividing by zero:

Getting a zero in the denominator cannot be wrong.

Getting a zero in the denominator is not wrong... what is wrong is trying to divide by zero. Using the IIF() function is q good way to trap trying to divide by zero.

And for the second part - Missing the False Clause......

BTW, am not sure if the false part is mandatory.. the access gurus might be able to shed some light on it. But it has worked for me without it.

Yes, it is required. From HELP:

The syntax is

IIf(expr, truepart, falsepart)

Part Description
expr Required. Expression you want to evaluate.
truepart Required. Value or expression returned if expr is True.
falsepart Required. Value or expression returned if expr is False.

Remarks
IIf always evaluates both truepart and falsepart, even though it returns only one of them. Because of this, you should watch for undesirable side effects. For example, if evaluating falsepart results in a division by zero error, an error occurs even if expr is True

I think you have been "lucky" so far. But I am happy you got it to work... And thanks for posting back...