Access Project filter on blank date

Posted on 2011-05-12
Last Modified: 2012-05-11
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.


Question by:czwg
    LVL 74

    Expert Comment

    by:Jeffrey Coachman
    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:



    Accepted Solution

    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!


    Author Closing Comment

    Not a solution to the problem posed: a work around.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    16 Experts available now in Live!

    Get 1:1 Help Now