[RESOLVED] Dealing with Null values in VBA Access Functions

Hi all,

I'm trying to write a simple function in Access VBA that I can use in a Jet SQL statement to return Null if a time value is greater than 20 hours (as this would have to be a data entry error).

I've got this far:

Code:

---

Function Cln(MyTime As Date)

If IsNull(MyTime) Then
Cln = Null
Else
If MyTime > 0.833333333333333 Then Cln = Null Else Cln = MyTime
End If

End Function

---

This works perfectly when passed a time but returns "#Error" if there is no time in the row (and therefore no MyTime). Clearly the IsNull element is not the right one to use.

Could anyone tell me how to handle instances where the value is missing in a custom access function as I've tried the standard Excel options (="", isNull, IsEmpty, is Nothing) and nothing seems to work.

Check out this article:
http://articles.techrepublic.com.com...1-5034252.html

It may help answer your question.

MyTime As Date cannot accept value of a blank cell because Date data type cannot be Null. That is why you get #Error on cell.
Try this function:

Code:

---

Public Function ValidTime(varTime As Variant) As Variant
    Dim t As Date
   
    ValidTime = ""
    'ValidTime = Null '-- returns 0 on cell
    If varTime <> "" Then
        On Error GoTo GetOut
        t = CDate(varTime)
        If t >= 0 And t <= TimeSerial(20, 0, 0) Then ValidTime = t
    End If
GetOut:
End Function

---

Thank you for your help