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

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!Fie开发者_开发百科lds.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.

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.

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.

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.

In the case of an IIF statement in VB, I think everything gets evaluated, irrespective of the initial condition. This is something that has to be worked around in IIF statements.

In the second statement, the Fields!Field**2**.Value from the first IIF statement has become Fields!Field**s**.Value. Its possible that the error in that statement is failing because of that as .Value will cause an error if Fields!Fields is nothing

I tried to reproduce this error in RS 2005 and I can't! I took the following dataset:

select 5 as field1, 0 as field2
union
select 10 as field1, 5 as field2
union
select null as field1, 5 as field2
union 
select 3 as field1, null as field2
union 
select null as field1, null as field2
union 
select 0 as field1, null as field2
union
 select null as field1,  0 as field2
union
 select 11 as field1, 10 as field2
union
 select PI() as field1, 0 as field2
union
select PI() as field1, PI() as field2

and used your expressions, with Jon Egerton's correction in the switch. I then did a pure no-checked division.

Both your expressions worked, and even when I divide by 0 I get "Infinity" or "Nan", but not #Error.

What happens if you replace your division by 1/0. Do you get #Error or do you get Infinity?