How To Pass a Filter Property to a Report AND Add More Criteria to that Filter (Access)

Question

I'm currently running Access 2007. I have a reporting manager that allows for the user to set parameters. Customize dates and allows for selecting what date field needs to be modified (timecreated, timemodified, txndate). The reporting aspect works perfectly.

Within the report (totalshipped), the user can double click the body of the report and it'll go from "big picture" summary to a detailed report (totalshippeddetailed). Basically in order for it to work properly, the dates from totalshipped and the customer name from the body (on double click) need to match in the totalshippeddetailed report when it opens. Here is an example of what I've tried:

Private Sub ftrReport_DblClick(Cancel As Integer)

DoCmd.OpenReport "TotalShippedDetailed", acViewReport, "", me.filter

End Sub

The report successfully opens up and passes the filter property (dates) onto the totalshipped detailed report, the problem is that I need to reference the [customerref_fullname] (customer) as well which I was doing by using this line:

[reports]![TotalShipped]![customerref_fullname] = [customerref_fullname]

This works as a reference to the customer but I need to combine the me.filter property with the reference to the customerref_fullname, an error occurs when I try:

Private Sub ftrReport_DblClick(Cancel As Integer)

  DoCmd.OpenReport "TotalShippedDetailed", acViewReport, "", me.filter & _
  [reports]![TotalShipped]![customerref_fullname] = [customerref_fullname] 

End Sub

Another way I tried it which works but doesn't allow the detailed version the report to be filtered by anything but the [txndate] :

DoCmd.OpenReport "TotalShippedDetailed", acViewReport, "", _
"[reports]![TotalShipped]![customerref_fullname] = [customerref_fullname] and [txndate] >= [forms]![frmrptdatemanager]![txtstartdate] And [txndate] <= [forms]![frmrptdatemanager]![txtenddate]  "

And lastly I took the above code and tried to reference the date field itself in the form which will give an error:

 DoCmd.OpenReport "TotalShippedDetailed", acViewReport, "", _
"[reports]![TotalShipped]![customerref_fullname] = [customerref_fullname] and [forms]![frmrptdatemanager]![cboDateField] >= [forms]![frmrptdatemanager]![txtstartdate] And [forms]![frmrptdatemanager]![cboDateField] <= [forms]![frmrptdatemanager]![txtenddate]  "

There are many more examples I've tried but none of them can accomplish my goal: allow the users to double click the body of the report to see the detailed report by customer with the same date range as the main report.

Please helP!!!!

One thing for sure... me.filter & _ [reports]![TotalShipped]![customerref_fullname] = [customerref_fullname] should have been me.filter & " and " & [reports]![TotalShipped]![customerref_fullname] & " = """ & [customerref_fullname] & """" .... or something more like that ... check the resultant string and it has to look like a legitimate filter. If that solves your problem, then we can ignore the other dead-ends. — Wellspring
and the above suggestion only works if me.Filter is not an empty string! I suggest you compose a good filter string first, then make the call using that filter string instead of the long concatenation of stuff. Easier to read, easier to debug. — Wellspring
the me.filter would never be an empty string as the only way to open the report is to pass the string to it. I'm not quite sure how to simplify it but I'll give it a shot for the above. @Triwaters The problem is that the string wouldn't be static. It would be changed all the time by the user. — Colton Mayers
I'm still getting a type mismatch error. Both how I did it originally and the above solution — Colton Mayers

John Mo John Mo · Accepted Answer · 2017-11-30T21:29:22

Remember that the filter parameter is like a WHERE clause without the WHERE keyword at the front of it.

What you're doing here:

  DoCmd.OpenReport "TotalShippedDetailed", acViewReport, "", me.filter & _
  [reports]![TotalShipped]![customerref_fullname] = [customerref_fullname]

You're taking the existing filter condition and adding to it, but you didn't include an AND keyword to link the current filter condition and the next condition.

You would need to do:

  DoCmd.OpenReport "TotalShippedDetailed", acViewReport, "", me.filter & _
  " AND [reports]![TotalShipped]![customerref_fullname] = '" & [customerref_fullname] & "'"

Notice there, also, that you have to concatenate the variable or control name into the filter string to pick up the value of the variable or control rather than the name of the variable or control. The value also has to be offset with correct punctuation when it's a string or date: single-tick quotes for string values and hash tag/pound symbols for dates.

To help develop and debug things like this, I recommend using a string variable to build up the entire filter condition. Inspecting the contents of the string variable or dumping the value to the immediate window (Debug.Print) before you use it as the filter parameter in the OpenReport method will help you catch missing ANDs, missing spaces, and missing punctuation in the filter condition.

Your DblClick method will look something like this:

Private Sub ftrReport_DblClick(Cancel As Integer)
  Dim FilterCondition as String

  FilterCondition = me.filter
  FilterCondition = FilterCondition & " AND [txndate] >= #" & [forms]![frmrptdatemanager]![txtstartdate] & "# AND [txndate] <= #" & [forms]![frmrptdatemanager]![txtenddate] & "#"

  Debug.Print FilterCondition

  DoCmd.OpenReport "TotalShippedDetailed", acViewReport, "", FilterCondition

End Sub

How To Pass a Filter Property to a Report AND Add More Criteria to that Filter (Access)

1 Answers