Filter report by one or more fields

EnginerdUNH
StarLounger
Posts: 86
Joined: 14 Aug 2019, 00:12

Filter report by one or more fields

Post by EnginerdUNH »

Hi,

I am working with some dummy data testing how to incorporate ideas before I utilize them in the database project I am working on. The piece that I am working on now is filtering a report based on one or more criteria. I did this by going into the query for the report data and typing in the criteria field the following for each of the fields I want to filter by:

Forms!frmRunReport!txtSupplierName or Forms!frmRunReport!txtSupplierName Is Null
Forms!frmRunReport!txtSerial or Forms!frmRunReport!txtSerial Is Null
Forms!frmRunReport!txtType or Forms!frmRunReport!txtType Is Null
Forms!frmRunReport!txtResult or Forms!frmRunReport!txtResult Is Null

After I saved and reopened the query, Access updated the criteria and or fields as expected to have all of the potential combinations of filter item or is null but when I run the query, no matter what options (or lack of) I have selected in the respective comboboxes on frmRunReport, no records appear. I went back to try and troubleshoot the query and filtering works when I apply the above method to one and only one field in the query but as soon as I try to add one or more additional filters, the query stops spitting out what I expect to see. I have attached a copy of the test database to this post.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter report by one or more fields

Post by HansV »

See the attached version. I moved the logic of filtering the data to the code behind the command button on the form; the report now simply has tblTestResults as record source.

Note: the report was grouped on non-existent expression TubeSerial. I changed this to SerialNumber.

Also, I made the Clear button set everything to Null instead of "", to make it possible to use IsNull.

TestReports.zip
You do not have the required permissions to view the files attached to this post.
Best wishes,
Hans

EnginerdUNH
StarLounger
Posts: 86
Joined: 14 Aug 2019, 00:12

Re: Filter report by one or more fields

Post by EnginerdUNH »

Hans, thank you so much!! Everything works now the way I want. I just have one question for you. One thing that I was never able to resolve is that I put in a page break in the section footer so that I could have the report print each supplier on a new page but it was always printing out a blank page at the end of the report. I noticed in the changes that you made, the page break was removed from the section footer yet it still prints as desired. Did you move it some place else in the report and I'm just missing where it is now? Or change the settings somehow? Thank you again.

User avatar
HansV
Administrator
Posts: 78236
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Filter report by one or more fields

Post by HansV »

I set the 'Force New Page' property of the group footer to 'Before Section'. In mots situations, it's better to use this property instead of a Page Break control.
Best wishes,
Hans