SQL -VB - Does it evaluate the else even when the if is true?

Question

I am trying to prevent #error being displayed in a report I am creating

This happens when I divide 2 numbers and one of them is zero

So I tried using an if/switch statement to check if either of the 2 number are 0 before doing the divide :

    =IIf(Fields!Field1.Value = 0 
            or Fields!Field2.Value = 0 
            or Not(IsNumeric(Fields!Field1.Value)) 
            or Not(IsNumeric(Fields!Field2.Value)), 
        0, 
        (Fields!Field1.Value/Fields!Field2.Value)*100
    )


    =Switch(
            Fields!Field1.Value = 0 or Fields!Fields.Value = 0, 0,
            IsNumeric(Fields!Field1.Value) or IsNumeric(Fields!Fields.Value), (Fields!Field1.Value/Fields!Fields.Value)*100
    )

Both of these still throw the error. It seems that the else condition is still evaluated even if the if statement is true

If I change the code to just print X or Y for the if and else, then it works - so there isn't an error in the if statement

This sems ridiculous to me

Please tell me I am doing something wrong? I can't believe the language would evaluate the else when the if is true

EDIT

So it seems that the else condition is evaluated. So how do you get around a potential divide by zero error?

here's the answer taken from : http://www.sqlservercentral.com/Forums/Topic442497-150-1.aspx#bm1115960

Another option (especially if you've got a report with many expressions that could result in divide by zero situations is to use a Custom Code function.

In the Code tab/window of Report Properties, enter something like the following:

Public Function DivideBy(ByVal Exp1, ByVal Exp2)
If Exp2 = 0 Then
DivideBy = 0
Else : DivideBy = Exp1 / Exp2
End If
End Function

Then insert the expression =code.DivideBy(Field!ToBeDivided.Value,Field!DividingBy.Value) into any cell that has the potential for divide by zero problems.

Answer 1

Yes, both the true and false parts of an IIf function get evaluated:

http://en.wikipedia.org/wiki/IIf#Side_Effects

Side Effects
Another issue with IIf arises because it is a library function: unlike the C-derived conditional operator, both truepart and the falsepart will be evaluated regardless of which one is actually returned. Consider the following example:

value = 10 
result = IIf(value = 10, TrueFunction, FalseFunction)

Although TrueFunction is the function intended to be called, IIf will cause both TrueFunction and FalseFunction to be executed.

Also consider this one:

a = 10 
b = 0 
result = IIf(b <> 0, a / b, 0) 

While the programmer intends to avoid raising an error by performing a division by zero, whenever b is zero the error will actually happen. This is because the code in the snippet is to be read as

a = 10 b = 0
_temp1 = a / b ' Error if b = 0
_temp2 = 0
_temp3 = b <> 0 result = IIf(_temp3, _temp1 , _temp2) 

This issue makes the IIf() call less useful than the conditional operator. To solve this issue, Microsoft developers had considered converting IIf to an intrinsic function; had this happened, the compiler would have been able to perform type inference and short-circuiting by replacing the function call with inline code.

Answer 2

Both Iif() and Switch() are function calls, so any parameters passed to them will be evaluated. They do not have the ability to short-circuit.

Answer 3

IIF is a function, and all the arguments to a function are evaluated before being the function itself is evaluated.

To prevent this issue, you will need to use a full If/Else statement.

Also related, if you're used to short-circuit evaluation of your boolean expressions (eg && in C-derived languages) you need to use AndAlso and OrElse in VB.Net.