How to write function from this IIF statement

[SF]

Hi,

I have the following

IIf((nz([07 Qtr 3],0)-nz([07 Qtr 2],0))=0,0,(nz([07 Qtr 3],0)-nz([07 Qtr
2],0))/(nz([07 Qtr 3],0)+nz([07 Qtr 2],0)))

It work well in query, Is there a way to code this into a function? I try
the following but it return #Error in query result

Public Function Convert2Dlb(AnyNumber As Variant) As Double
'CDbl(nz([FY 07],0))
Convert2Dlb = CDbl(Nz(AnyNumber, 0))
End Function

Public Function PCal(Value1 As Double, Value2 As Double) As Double
Select Case Convert2Dlb(Nz(Value2, 0)) - Convert2Dlb(Nz(Value1, 0))
Case Is = 0
PCal = 0
Case Else
PCal = (Convert2Dlb(Value2) - Convert2Dlb(Value1)) /
(Convert2Dlb(Value1) + Convert2Dlb(Value2))
End Select
End Function

SF

 

[Ken Snell \(MVP\)]

A variable that is a Double type cannot contain a Null value. Your code
suggests that you might indeed have a Null value for one of the arguments
being transferred to the PCal function.

Also, I think your logic is incorrect. You need to test the denominator to
see if it's a zero, not the numerator. So try this:

Public Function Convert2Dlb(AnyNumber As Variant) As Double
'CDbl(nz([FY 07],0))
Convert2Dlb = CDbl(Nz(AnyNumber, 0))
End Function

Public Function PCal(Value1 As Variant, Value2 As Variant) As Double
Dim ResultNum As Double, ResultDenom As Double
ResultNum = Convert2Dlb(Value2) - Convert2Dlb(Value1)
ResultDenom = Convert2Dlb(Value1) + Convert2Dlb(Value2)
Select Case ResultDenom
Case Is = 0
PCal = 0
Case Else
PCal = ResultNum / ResultDenom
End Select
End Function

 

[SF]

Thank you for your prompt response. This function works perfectly

SF

A variable that is a Double type cannot contain a Null value. Your code
suggests that you might indeed have a Null value for one of the arguments
being transferred to the PCal function.

Also, I think your logic is incorrect. You need to test the denominator to
see if it's a zero, not the numerator. So try this:

Public Function Convert2Dlb(AnyNumber As Variant) As Double
'CDbl(nz([FY 07],0))
Convert2Dlb = CDbl(Nz(AnyNumber, 0))
End Function

Public Function PCal(Value1 As Variant, Value2 As Variant) As Double
Dim ResultNum As Double, ResultDenom As Double
ResultNum = Convert2Dlb(Value2) - Convert2Dlb(Value1)
ResultDenom = Convert2Dlb(Value1) + Convert2Dlb(Value2)
Select Case ResultDenom
Case Is = 0
PCal = 0
Case Else
PCal = ResultNum / ResultDenom
End Select
End Function

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

Hi,

I have the following

IIf((nz([07 Qtr 3],0)-nz([07 Qtr 2],0))=0,0,(nz([07 Qtr 3],0)-nz([07 Qtr
2],0))/(nz([07 Qtr 3],0)+nz([07 Qtr 2],0)))

It work well in query, Is there a way to code this into a function? I try
the following but it return #Error in query result

Public Function Convert2Dlb(AnyNumber As Variant) As Double
'CDbl(nz([FY 07],0))
Convert2Dlb = CDbl(Nz(AnyNumber, 0))
End Function

Public Function PCal(Value1 As Double, Value2 As Double) As Double
Select Case Convert2Dlb(Nz(Value2, 0)) - Convert2Dlb(Nz(Value1, 0))
Case Is = 0
PCal = 0
Case Else
PCal = (Convert2Dlb(Value2) - Convert2Dlb(Value1)) /
(Convert2Dlb(Value1) + Convert2Dlb(Value2))
End Select
End Function

SF