Run-time error 94; Invalid use of Null

[williams485]

Hello Excel Forum,
I'm running a macro that retrieves data from database and sorts it based on a field, which contains a date value. If the value of the field is less than the current date or doesn't contain anything (i.e. is null), I want to do something with it. The code is as follows:

If IsNull(RCD.fields(6).Value) Or DateValue(RCD.fields(6).Value) <= Date Then
      RCD.movenext
End If

However, when the code reaches this line I get 'invalid use of null'. I've tested the value of the field in the Immediate window and the field value on the very first data line is Null. How can I achieve what I'm trying to do?

Thanks

[JosephP]

I reckon you'll need two separate tests since an Or conditions will test both and you can't use datevalue with null

If IsNull(RCD.fields(6).Value) then
         RCD.Movenext
      elseif DateValue(RCD.fields(6).Value) <= Date Then
      RCD.movenext
End If

or change your query so that it returns something other than null

[Norie]

You need to separate the 2 criteria as you can't use DateValue with Null, which is why you get the error.

boolDoIt = IsNull(RCD.Fields(6).Value)

If Not boolDoIt Then
    boolDoIt = DateValue(RCD.Fields(6).Value)
End If

If boolDoIt Then
       RCS.MoveNext
End If

[williams485]

Thanks JP and Norie. I've split the conditions and it's working as intended.