Filtering for a Null date

[Peter Kinross]

I am trying to count the number of records for given values of dates (ThisDateIn).

Code: Select all

rsFunds.Filter = "[PolSalesNo] = " & SalesNo & " AND [DateIn] = #" & Format(ThisDateIn, "mm/dd/yyyy") & "#"
Set rsCopyFunds = rsFunds.OpenRecordset
rsCopyFunds.MoveLast

This works without error if I am filtering when ThisDateIn not null and >0. But I need it to work when ThisDateIn is Null or 0 as well.
For the case when ThisDateIn is Null or 0, there are actually 11 records (by manually counting them), but I get a "No current record" error at the .MoveLast line.
Anyone know how I can filter?

[HansV]

Try

Code: Select all

Dim strFilter As String
If IsNull(ThisDateIn) Then
    strFilter = "[PolSalesNo] = " & SalesNo & " AND [DateIn] Is Null"
Else
    strFilter = "[PolSalesNo] = " & SalesNo & " AND [DateIn] = #" & Format(ThisDateIn, "mm/dd/yyyy") & "#"
End If
rsFunds.Filter = strFilter
Set rsCopyFunds = rsFunds.OpenRecordset
If rsCopyFunds.EOF Then
    ' No records found
Else
    rsCopyFunds.MoveLast
    ...
End If

[Peter Kinross]

I put the criteria into a query and peeked at the SQL and got the clue for using "Is Null" from that. I think I will have to add the [DateIn]=0 as well.
But your layout is MUCH more simple than mine. So thanks Hans - big help.
I have become a walking example of 'Use it or loose it'. I update my Db's code sporadically now, and have thus lost the at call knowledge that I used to have (to a limited extent compared to some of you guys). So grateful to have everyone at the Lounge. REALLY APPRECIATED.