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
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