Divide by zero error-trapping

[rosenn]

Hello all,

I've got a little function (code is below) in a supply database, that takes in Quantity Authorized (QAUTH) and Quantity on-hand (QOH), and divides them, to get the %, and then returns a string color-code value (G-green, A-Amber, R-Red) based on that percentage. Problem is my code doesn't correctly error-trap if the fields haven't ever been entered. From what I've written, using IsNull and IsEmpty, if either of the input values (QOH, QAUTH) have never been entered, the function should return "R". This doesn't work, and it returns "#ERROR". My thinking is that, regardless of my code, the subsequent code (which should never run in these situations) still senses the divide by zero. Perhaps the error is for a different reason. I'm stumped.

Am I missing something that would fix this error? Do I need to go back to the tables and the form controls and make those values default to zero? I should think that there's a more elegant solution than that. Any ideas?

Here's the code. Thanks in advance,

NR


Function StatusColor(QOH As Integer, QAUTH As Integer) As String
On Error GoTo ErrHandler

' This line HERE should trap the error, but for some reason its not. Ideas?
If IsNull(QOH) Or IsNull(QAUTH) Or IsEmpty(QOH) Or IsEmpty(QAUTH) Or (QOH <= 0) Or (QAUTH <= 0) Then
StatusColor = "R"
Exit Function
Else
Strength = (QOH / QAUTH)
End If
If Strength < 0.7 Then
StatusColor = "R"
Else
If (Strength >= 0.7 And Strength < 0.8) Then
StatusColor = "A"
Else
If Strength = 1 Then
StatusColor = "G"
Else
StatusColor = "O"
End If
End If
End If

Exit Function

ErrHandler:
MsgBox Err.Number & " - " & Err.Description
Err.Clear
End Function

 

[selenau837]

I'm no guru, but is the exit fuction suppose to between the If, and the first Else?

If IsNull(QOH) Or IsNull(QAUTH) Or IsEmpty(QOH) Or IsEmpty(QAUTH) Or (QOH <= 0) Or (QAUTH <= 0) Then
StatusColor = "R"
[B][COLOR="Red"]Exit Function[/COLOR][/B]
Else
Strength = (QOH / QAUTH)
End If
If Strength < 0.7 Then
StatusColor = "R"
Else
If (Strength >= 0.7 And Strength < 0.8) Then
StatusColor = "A"
Else
If Strength = 1 Then
StatusColor = "G"
Else
StatusColor = "O"
End If
End If
End If

Exit Function

ErrHandler:
MsgBox Err.Number & " - " & Err.Description
Err.Clear
End Function

 

[rosenn]

Yes, that's intentional.

The way I wrote it, and from my understanding between the difference of how an If-then-else loop works vs. the IIF function, only the true path of the if-then-else loop will run. So if the values are Null, or Zero, or negative, the function is supposed to assign a value of "R" to StatusColor, and then exit the function before anything else get's done. For some reason, that's not happening if either value is empty.

I should point out that I get no compiler error (not surprising), but in cases where the value of either field in question (QOH or QAUTH) is empty, the function returns "#ERROR".

 

[checoturco]

try


If IsNull(me.QOH) Or IsNull(me.QAUTH) Or IsEmpty(me.QOH) Or IsEmpty(me.QAUTH) Or (QOH <= 0) Or (QAUTH <= 0) Then

i assume that QOH,... are controls and you are running this code in the form with these controls( if not, change the me to foms!<form_name>!control_name)

 

[nateobot]

Pretty sure you can remove the IsNull and IsEmpty references since the function arguments are not Optional.

Try this:

Function StatusColor(QOH As Integer, QAUTH As Integer) As String
On Error GoTo ErrHandler

' This line HERE should trap the error, but for some reason its not. Ideas?
If IsNull(QOH) Or IsNull(QAUTH) Or IsEmpty(QOH) Or IsEmpty(QAUTH) Or (QOH <= 0) Or (QAUTH <= 0) Then
StatusColor = "R"
Exit Function
Else
Strength = (QOH / QAUTH)
End If
If Strength < 0.7 Then
StatusColor = "R"
Else
If (Strength >= 0.7 And Strength < 0.8) Then
StatusColor = "A"
Else
If Strength = 1 Then
StatusColor = "G"
Else
StatusColor = "O"
End If
End If
End If

Exit Function

ErrHandler:
MsgBox Err.Number & " - " & Err.Description
Err.Clear
End Function

You could also try putting in:
QOH = Nz(QOH, 0)
QAUTH = Nz(QAUTH, 0)

Right after your On Error line.

 

[colette]

Try testing for quotes also

Hi,
I don't think this is the solution, but another thing I often do is if var=""

To troubleshoot this, try seeing what your variable values are as the function begins either by stepping through the code or by putting a msgbox that displays the variables there. This might help determine what you should be testing for in the R situation.
Colette

 

[rosenn]

Thanks. You led me to the solution.

I can't believe I forgot this. It's sad when senility strikes....I'm not even 40.

My mistake: I called the function using the following:

StatusColor(Me.txtQuantityOnHand, Me.txtQuantityAuthorized)

when what I should have done was call them as follows:

StatusColor (Nz(Me.txtQuantityOnHand,0), Nz(Me.txtQuantityAuthorized,0))

That fixed it. It dawned on me when I changed how I called the function from an update query to a button on a form. That initiated the Error #94 - invalid use of Null, which reminded me that you can't place a null value as an argument in function. That's why putting the Nz(___,0) into the body of the function won't fix it, but it will if you use them when you call the function.

All fixed, but the sad part is that I went through this same problem 3 years ago, and I forgot....until now. Better late than never.

Thanks for the tip, which led to me solving this problem.