Divide by 0! and the tempermental conditional IIf
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
‘examples:
‘ 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
=Code.DivideXbyY(x,y)
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: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1708914&SiteID=1
Author: Jerry Hung
