I have encountered a problem when returning a record set which contains a Null value. Please can any one help

I am trying to determine if a date field against a client is empty but if it is empty, when I try to get the value, I get an error message when it gets to dteDateCreated - "Invalid Use Of Null"

Dim SQLText As String
Dim rs As Recordset
Dim strClientCode
Dim dteDateCreated As String
Dim strCode As String

strClientCode = [Forms]![frmClient].[ClientCode]

SQLText = "SELECT tblClient.ClientCode, tblClient.DateCreated " & vbCrLf & _
"FROM tblClient " & vbCrLf & _
"WHERE (tblClient.ClientCode) = " & strClientCode & ";"

Set rs = CurrentDb.OpenRecordset(SQLText)
strCode = rs.Fields("ClientCode").Value
dteDateCreated = rs.Fields("DateCreated").Value

It works OK if there is a date present.

I am using Access 2000 on Windows XP.

Re: Null Value Error in recordset



Checkout the NZ function, chnage your code to

dteDateCreated = nz(rs.Fields("DateCreated").Value,0)

Re: Null Value Error in recordset



Thanks for the suggestion. I will give it a go.

I got round the problem by

If IsNull(dteDateCreated = rs.Fields("DateCreated").Value) Then .......