Function returns #Error

[shkrebs]

I'm using a public function to calculate the difference between to numbers in %.
To make sure I don't get a "Division by Zero" error I'm using a switch.
The numbers come from a table where the datatype is Currency.

The function works - but still it returns #Error on my report when there is no data in the fields...


Public Function DiffPct(Switch As Integer, Numero1, Numero2 As Double) As Double

Dim VARWork As Double

If IsNull(Switch) Then
DiffPct = 0
Else
VARWork = ((Numero1 / Numero2) - 100)
DiffPct = 100 * (Abs(VARWork) - Fix(Abs(VARWork)))
End If
End Function


I'm calling the function from a SQL where the return value is stored in a variable (Benefit), which I'm using as datasource on my report.


"SELECT comunidad.Comunidad_Autonomia, [Codigo Postal].texto, " & _
"Sum(TempTabla1.NrDeLibros) AS SumDeLibros, Sum(TempTabla1.FactSin) AS FactSinIVA, " & _
"Sum(TempTabla1.FactCon) AS FactNeta, ([FactNeta]*166.386) AS FactNetaPtas, " & _
"DiffPct([SumDeLibros], [FactNeta], [FactSinIVA]) AS Benefit " & _
"FROM TempTabla1 RIGHT JOIN ([Codigo Postal] "


Can anyone tell why?

Thanks in advance
Søren

 

[Travis]

Public Function DiffPct(ByVal Switch As Integer, ByVal Numero1 As Double, ByVal Numero2 As Double) As Double

Dim VARWork As Double
    
    'Converts Nulls to Zero and converts the value back to an integer
    Switch = CInt(Val(Switch & ""))
    'Converts Nulls to Zero
    Numero1 = Val(Numero1 & "")
    'Converts Nulls to Zero
    Numero2 = Val(Numero2 & "")

    If Switch = 0 Or Numero2 = 0 Then
        DiffPct = 0
    Else
        VARWork = ((Numero1 / Numero2) - 100)
        DiffPct = 100 * (Abs(VARWork) - Fix(Abs(VARWork)))
    End If

    
End Function

 

[shkrebs]

WOW Travis - of course.

Yes, my problem was that there was no value to test on....

From now on you will be a part of my eveningprayer

Thank you
Søren