Why do I get an error?

[systemx]

Hi all,

Not a problem - but seeking answers to why an error occurs.

Here is a snippet of my code -

Private Sub lstResults_DblClick(Cancel As Integer)
Dim Start As Date

Start = DLookup("Date_Commenced", "tblEnquiries", "[ID]=" & Me.lstResults.Value)
If IsNull(Start) Then

strSQL = "UPDATE tblEnquiries SET Date_Commenced =" & Chr(34) & Date & Chr(34)
strSQL = strSQL & " WHERE ID = " & Me.lstResults.Value & ";"
CurrentDb.Execute strSQL

strSQL = "UPDATE tblEnquiries SET Time_Commenced =" & Chr(34) & Time & Chr(34)
strSQL = strSQL & " WHERE ID = " & Me.lstResults.Value & ";"
CurrentDb.Execute strSQL

MsgBox "Thank you. The current date and time have been recorded as the start time for work on the enquiry. This data will be used to help calculate accurate work rates."

DoCmd.OpenForm "frmPSclose", , , "[ID] = " & Me!lstResults.Value

Else

MsgBox "Work has commenced on this enquiry on " & Start & ". You may view the details or close the enquiry if it has been completed"

DoCmd.OpenForm "frmPSclose", , , "[ID] = " & Me!lstResults.Value

End If

When run, I get an 'Invalid use of Null' error.

I resolved the problem by declaring 'Start' as a Variable. Now it works perfectly. But I cannot understand why it would not like it being a date, given the field in the table is a Date/Time type.

Can anyone enlighten me on this?

Cheers

Rob

 

[Smart]

Start = DLookup("Date_Commenced", "tblEnquiries", "[ID]=" & Me.lstResults.Value)
If IsNull(Start) Then

Try this:
Start = Nz(DLookup("Date_Commenced", "tblEnquiries","[ID]=" & Me.lstResults.Value), "")

If Start = "" Then