Code to hide "NaN" and "Infinity"

Question

Code to hide "NaN" and "Infinity"

Sam Peascod

SSCrazy

Points: 2497
More actions
May 16, 2007 at 12:43 am

#371239

I have a formula in the Report Properties -> Code section which calculates the percentage difference between two input values. Currently the formula returns "NaN" if both input values are zero, and "Infinity" if the denominator is zero. I want the formula to return 0 instead of NaN or Infinity.
I've tried the following code:
PcDiff = Iif(LastYear = 0 OR IsNull(LastYear), 0, (ThisYear/LastYear) - 1)
But I get the following compiler error:
Native compiler return value: ‘[BC42206] Maximum number of warnings has been exceeded.’.
How can I do this?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I think 1 issue is that you need to replace the IsNull with LastYear is Nothing as IsNull is not a VB.NET function, or you could you use LastYear = System.DBNull.Value. So your code would be:

PcDiff = Iif(LastYear = 0 OR LastYear = System.DBNull.Value, 0, (ThisYear/LastYear) - 1) Or

PcDiff = Iif(LastYear = 0 OR LastYear Is Nothing, 0, (ThisYear/LastYear) - 1)

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

Sam Peascod SSCrazy Points: 2497 More actions · Answer 2

Hi Jack,

I've just tried both of those and got the same error. My entire function is here, if it helps:

---------------

Public Shared Function PcDiff(LastYear As Single, ThisYear As Single) As Single

PcDiff = Iif(LastYear = 0 OR LastYear = System.DBNull.Value, 0, (ThisYear/LastYear) - 1)

'PcDiff = (ThisYear/LastYear) - 1

End Function

--------------

Thanks,

Sam

Chris Hinchcliffe Valued Member Points: 67 More actions · Answer 3

Hi Sam,

I was having the same problem and came across this solution.

Enter this in the custom code : -

Public Shared Function Ratio(Num1 as double, Num2 as double) AS object

IF ISNOTHING(Num2) Or Num2 = 0 Then

Ratio = 0

ELSEIF Num1 = 0 THEN

Ratio = 0

ELSE

Ratio = Num1 / Num2

END IF

End Function

Then if you use soemething like this in the body of the report : -

=Code.Ratio(Fields!yourfield1.Value, Fields!yourfield2.Value)