"Safe" division function help

[johnnygeo]

I frequently need to run queries in Access dividing one column by another and I get sick of always having to put the division inside an iif() statement to try and trap out zero values to avoid division by zero errors. So I wrote the following function:

Function safeDiv(numerator As Variant, denominator As Variant, Optional divByZero As Variant = Null)

    If IsNull(denominator) Or denominator = 0 Then
        safeDiv = divByZero
    Else
        If IsNull(numerator) Then
            safeDiv = 0
        Else
            safeDiv = numerator / denominator
        End If
    End If
Exit Function

End Function

so for example:
safeDiv(10,5) = 2
safeDiv(10,0) = Null
safeDiv(10,0,0) = 0

works pretty well, except the resulting column is a text field. I tried defining the function as Double, but then the function crashes if I try to assign Null to it. Is there any way to make the successful division be a number but still allow the failures to be null?

Thanks,
Johnny Geo

 

[SkipVought]

I ran your function, with NULL (returns a zero) and numeric just fine!

The Null value indicates that the Variant contains no valid data. Null is not the same as Empty, which indicates that a variable has not yet been initialized. It is also not the same as a zero-length string (""), which is sometimes referred to as a null string.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[mintjulep]

As Skip has noted, you might want to add a few other tests for the other possible cases.

A select case statement might be a bit cleaner.

And finally, although possibly no longer relevant, you can coerce variable types. cInt(), cDbl(), cDbl(), and I think even cStr() and some others are available. See help for variable type conversion.

 

[misterstick]

Function safeDiv(numerator As Variant, denominator As Variant, Optional divByZero As Variant = Null) [i]As Double[/i]

mr s. <

 

[johnnygeo]

Perhaps I wasn't clear enough.

Using my code above, all cases work OK, i.e. without errors, but the successful divisions come out as text. So I actually get:
safeDiv(10,5) = "2"
safeDiv(10,0) = Null
safeDiv(10,0,0) = "0"

If, as misterstick suggests, I define the function like

Function safeDiv(...) as Double

, then the line

safeDiv = divByZero

is trying to assign a null value to a Double variable, so I get:

safeDiv(10,5) = 2
safeDiv(10,0) = #Error
safeDiv(10,0,0) = 0

I get the same result by using cDbl() with the original code:
cDbl(safeDiv(10,5)) = cDbl("2") = 2
cDbl(safeDiv(10,0)) = cDbl(Null) = #Error
cDbl(safeDiv(10,0,0)) = cDbl("0") = 0