Access Project filter on blank date

I have been using Access forms and reports with extensive vba code as part of my Planchest application for nearly ten years. However, this simple requirement has me stumped.

I'm trying to generalise a method of allowing users to filter and sort in a form using standard Access menu options and have the same filter and sort apply in the corresponding report.

To this end, I have a dozen paired forms and reports which use the same recordsource and have same named controls.

When the user clicks a report button from a filtered form, they are prompted to give the filter a friendly name (to complete a label on the report) and then on the open event of the report, the corresponding form filter and orderby properties are retrieved and applied.

This works fine for almost any filter condition except for a date field where the date is blank. Then I get the error:

Run-Time error '7874': can't find the object '|1'

In my Report open event I have:

Private Sub Report_Open(Cancel As Integer)
    AdminInfo Me
    SetLogos Me
    SourceFilterOrder Me, Forms("frmProjectActionsIn")
End Sub

This code is generic to all reports:

Public Sub SourceFilterOrder(ByRef rpt As Report, ByVal frm As Form)
    rpt.RecordSource = frm.RecordSource
    rpt.Filter = SimpleFilter(frm)
    rpt.FilterOn = frm.FilterOn
    rpt.OrderBy = SimpleOrder(frm)
    rpt.OrderByOn = frm.OrderByOn
End Sub

Function SimpleFilter(frm As Form) As String
    With frm
        If .FilterOn And Not IsNull(.Filter) Then
            SimpleFilter = Replace(Replace(.Filter, frm.Name & ".", ""), "[]", "")
        End If
    End With
End Function

Function SimpleOrder(frm As Form) As String
    With frm
        If .OrderByOn And Not IsNull(.OrderBy) Then
            SimpleOrder = Replace(Replace(.OrderBy, frm.Name & ".", ""), "[]", "")
        End If
    End With
End Function

(These last two strip the form name whether or not in square brackets from the filter and sort expressions)

My underlying form recordsource may already include dynamically applied SQL 'where' conditions and although I could append the filter condition before applying it to the  report recordsource, this would mean I had failed to simply clone the filter and order from the form to the report, surely a simple enough objective.


czwgIT Systems ManagerAsked:
Who is Participating?
czwgConnect With a Mentor IT Systems ManagerAuthor Commented:
Whilst I'm grateful for boag2000 for his proposed solution, it didn't work for me.

His approach was to use

Me.Filter = "Isnull(Orderdate)"

But while this works in an MDB it doesn't appear to do so in an ADP project.

I think that the expression IsNull(field) isn't valid as an Access filter property against an SQL database. field IS NULL works as a where condition but not as a filter property.

All I was trying to do was read a filter condition out of a form and apply it to a report manipulating the syntax if necessary. Should be simple?

Well, I have admitted defeat. I have written code to parse the form filter condition(s) one by one. If a condition includes a date, I add it to the Where clause of the recordset SQL. Otherwise, I leave it as a filter condition. I then apply the recordset, reassemble the filter string and apply this.

Not very elegant!

Jeffrey CoachmanMIS LiasonCommented:
This is the inherent issue with try to do anything with Null values.

I can't say that I understand all that you are doing here...

But, if I understand the main issue (Filtering an associated report for Null Dates), you can try something like this:


czwgIT Systems ManagerAuthor Commented:
Not a solution to the problem posed: a work around.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.