Public Function nTechlevel(ByVal varAppointed As Variant) As Integer
'Use a Variant parameter, in case record contains Null
On Error GoTo nTechlevel_err
Dim nMonths As Integer
Dim dteAppointed As Date
If Not IsNull(varAppointed) Then
If IsDate(varAppointed) Then
dteAppointed = CDate(varAppointed)
Else
Err.Raise vbObjectError + 1001, "nTechLevel", "Input is not recognisable as a date."
End If
Else
Err.Raise vbObjectError + 1002, "nTechLevel", "Input was null."
End If
If dteAppointed > Now() Then
Err.Raise vbObjectError + 1003, "nTechLevel", "Future Dated appointment."
End If
nMonths = DateDiff("m", dteAppointed, Now())
'reduce by incomplete quarters
nMonths = nMonths - (nMonths Mod 3)
nTechlevel = (nMonths / 3) + 1
nTechlevel_exit:
Exit Function
nTechlevel_err:
' assign a value that would be easy to spot
' so you will know something has gone wrong
'You can assign different values to show the cause of the error
Select Case Err.Number
Case (vbObjectError + 1003)
nTechlevel = -9996 'Future Dated Appointment
Case (vbObjectError + 1002)
nTechlevel = -9997 'Null input
Case (vbObjectError + 1001)
nTechlevel = -9998 'Not a recognisable date
Case Else
nTechlevel = -9999 'Other error
End Select
'Alternateley (or also), issue an error message (de-comment the MsgBox line, below).
'the error message on its own can make it more difficult to
'work out which record is causing the error
'MsgBox "An error has occurred." & vbCrLf & "Number = " & Err.Number & vbCrLf & Err.Description & vbCrLf & Err.Source, vbCritical + vbOKOnly, "Error"
End Function