Divide by Zero error module

[gdesreu]

Does anyone have a module that will work like excels iferror for access?
The iif statements for this are getting cumbersome for many of my calculations and I would prefer something like iferror([field1]/[field2],null) and NOT calculation: IIf([field2]=0,Null,[field1]/[field2]). This of course is a simple calculation and not representative of the scientific calcs I deal with day in and day out.
Thanks

 

[gdesreu]

I tried Googling to see if anyone had created a custom IFERROR function that could be used, but unfortunately I could not find any.
Every instance I came across either uses the methodology you are currently using, or the IIF(ISERROR(... functionality.

Here is a similar thread where revans says he wrote a function for it, but the one he cited from memory doesn't seem to work if the denominator is a null. It appears he got one to work though based on his statement. His concept is not exactly what I was looking for but would definitely help in some scenarios.
https://www.mrexcel.com/forum/microsoft-access/608516-handling-division-zero-access.html

 

[Joe4]

Here is a variation of the UDF in that function that will handle both cases where the denominator is 0 or null.

Function MyDivision(numerator As Variant, denominator As Variant) As Single

    Dim temp As Single

    If IsNumeric(numerator) And IsNumeric(denominator) And (denominator <> 0) Then
        temp = numerator / denominator
    Else
        temp = 0
    End If

    MyDivision = temp

End Function

But I thought you were looking for something more generic (that would handle more errors than just division).

 

[gdesreu]

Here is a variation of the UDF in that function that will handle both cases where the denominator is 0 or null.

Function MyDivision(numerator As Variant, denominator As Variant) As Single

    Dim temp As Single

    If IsNumeric(numerator) And IsNumeric(denominator) And (denominator <> 0) Then
        temp = numerator / denominator
    Else
        temp = 0
    End If

    MyDivision = temp

End Function

But I thought you were looking for something more generic (that would handle more errors than just division).

Well that would be great if possible but I was looking for a divide by 0 function similar to how excels iferror works. This excel formula "IFERROR((A1/B1),"")" solves ALL of my problems because A1/B1 can be any formula. If I could get an access function that just does even divide by zero errors then that solves 99.9% of my access problems as most of the calculations are very complicated ratios.

 

[Joe4]

So, did the variation I put up there for you in my last post do what you want?
You would just use it like:
MyDivision(numerator,denominator), i.e.
MyDivision([Field1],[Field2])

 

[gdesreu]

So, did the variation I put up there for you in my last post do what you want?
You would just use it like:
MyDivision(numerator,denominator), i.e.
MyDivision([Field1],[Field2])

its close except it should return null for 1/0 and 1/null.

 

[Joe4]

Just a small tweak:

Function MyDivision(numerator As Variant, denominator As Variant) As Variant

    Dim temp As Variant
    
    If IsNumeric(numerator) And IsNumeric(denominator) And (denominator <> 0) Then
        temp = numerator / denominator
    Else
        temp = Null
    End If

    MyDivision = temp

End Function

 

[gdesreu]

Just a small tweak:

Function MyDivision(numerator As Variant, denominator As Variant) As Variant

    Dim temp As Variant
    
    If IsNumeric(numerator) And IsNumeric(denominator) And (denominator <> 0) Then
        temp = numerator / denominator
    Else
        temp = Null
    End If

    MyDivision = temp

End Function

yep that worked! I appreciate it! Ill try and test it on some lengthy formulas to see if its hits any snags. I cant see where it would as long as my numerator and denominator are correct, but then again every time I think that I get burned on something. Thanks Again!

 

[Joe4]

You are welcome!

 

[Norie]

gdesreu

Just out of curiosity, could you post an example of the type of formulas you are working with and perhaps some info on the data structure?

 

[Micron]

As usual, I'm probably not seeing the whole requirement so it seems simpler than it really is. What I haven't gleaned from this is exactly how the function is supposed to work (I mean is it supposed to handle a bunch of error types or just division by zero)?

First, to address the question about passing data to a function from a form control: that is the simplest use; passing an object like the control itself is no more difficult, it's just a matter of writing the function to expect one or the other (or even either if necessary). So if you wanted, a function could provide a simple custom message, or a message with an input prompt for a value, or substitute value, or... that's up to you. If this snippet was in a standard module, it could be called from any form to provide one validation (div by zero) and return either the answer or Null. If need be, the operation could be part of the function call (multiplication, subtraction, division, whatever).

In the immediate window, this would run the function - Call divByZero(5, 0)
Or as part of form code, e.g.
lngSomeVariable = divByzero (Me.txt1, Me.Txt2)

Sub divByZero(x As Long, y As Long) As Variant
On Error GoTo errHandler
divByZero =   x / y
Exit Sub

errHandler:
If Err.Number = 11 Then
   MsgBox "Cannot divide by zero"
  divByZero = Null
   Exit Sub
Else
  Msgbox "Error " & Err.Number & ": " & Err.Description
End If
End Sub

After all this, seems I missed a similar post. Rather than dump it, I'll post it anyway.