Developing Matt

My Technical Journal

Divide by 0! and the tempermental conditional IIf

leave a comment »

When working with reports there is this little glitch with the conditional iif statement.  The problem is that even if you check for 0 in your value the engine still tries to process the full statement. If your value = 0 then this will fail:  iif(value=0,1,1/value).  Result: #Error 

Stated again: the engine still tries to process 1/value even though it shouldn’t ever hit it.  So here’s a workaround.


Add the following code to your report.  (report properties…code tab) 

Public Shared Function DivideXbyY(ByVal X As Decimal, ByVal Y As Decimal) As Decimal

    DivideXbyY = IIF(Y=0, 0, X /iif(Y=0,1,Y))

End Function


‘ DivideXbyY(1,0) returns 0

‘ DivideXbyY(1,1) returns1

This line: iif(Y=0,1,Y) makes sure the code evaluates correctly.  It technically won’t ever run at runtime, but you won’t get an #error now. 


Now you can just call it with an expression



Ignore the ‘Unrecognized identifyer’ error warning.  The code compiles at runtime so in design mode it doesn’t know what you are referencing. 


Code found:

Author: Jerry Hung



Written by matt

May 23, 2008 at 9:47 am

Posted in Report Services

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s

%d bloggers like this: