Access Project filter on blank date

Posted on 2011-05-12
Medium Priority
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
  • 2
LVL 74

Expert Comment

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

czwg earned 0 total points
ID: 35873306
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

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

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

850 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