#Error using IIF and divide by zero

I am getting an error in a calculated field that could potentially divide by zero, even though I'm using an IIF. The column displays in the report as "#Error". My expression looks like this:

= IIF(Fields!Qty.Value = 0, "None", Fields!Hours.Value / Fields!Qty.Value)

I have successfully used this approach with INT fields, but this time the Hours field is a NUMERIC(9,2). My workaround is to do this:

IIF(Fields!Qty.Value = 0, "None", IIF(Fields!Qty.Value = 0, 42, Fields!Hours.Value) / Fields!Qty.Value)

I guess the 42 is cast to an INT inside the second IIF and the calculation works.

What's strange is that the division would even be carried out in the event of Qty = 0 from the first IIF, because the expression should just evaluate to "None" and that would be that.

Has anybody run into this problem? Is my workaround the recommended approach?

-Larry

Lawrence

Try

IIf(Fields!Income2.Value = 0, nothing,Fields!Income.Value/Fields!Income2.Value)

This works for me when my value is zero

Ham

|||

Hi Larry,

I recommend to add a custom code function for the division (in Report -> Report Properties -> Code):

Public Function Divide(ByVal first As Double, ByVal second As Double) As Double
If second = 0 Then
Return 0
Else
Return first / second
End If
End Function

Then, modify the expression accordingly:

= IIF(Fields!Qty.Value = 0, "None", Code.Divide(Fields!Hours.Value, Fields!Qty.Value))

-- Robert

|||

Thanks Robert, that's a good (dare I say) workaround. I'm still curious why the IIF errors out with the double division but works with integer division.

Also, the Edit Expression dialog has the "Divide" text underlined in red, but my project builds successfully and runs ok too. Any idea why it might think it's invalid?

-Larry

|||

Hi Ham,

Looks like I would still have to do two nested IIF statements -- one for my "None" message, and the other to return Nothing. I'm trying to avoid that. But the Code.Divide approach is working, so I'm on my way.

Thanks.

-Larry