Forum Discussion
Filter form
It appears that one problem lies in the where clause in the SQL for the report's recordsource. It uses "OR" to apply either of two criteria to the records. If records pass one of those two criteria, it doesn't matter if the other would eliminate them. All of the records are passing the first part of the WHERE clause, before the OR. The second part would otherwise limit records only to the selected department, but that doesn't matter. They are all passed through in the first part anyway.
SELECT tblAppendPQ.*, tblAppendPQ.[Responsible Area]
FROM tblAppendPQ
WHERE (((tblAppendPQ.[Due Date])>=[forms]![frmFilter_ShortByMonth]![txtDateFrom] And (tblAppendPQ.[Due Date])<[forms]![frmFilter_ShortByMonth]![txtDateTo]+1)) OR (((tblAppendPQ.[Due Date])>=[forms]![frmFilter_ShortByMonth]![txtDateFrom] And (tblAppendPQ.[Due Date])<[forms]![frmFilter_ShortByMonth]![txtDateTo]+1) AND ((tblAppendPQ.[Responsible Area])=[forms]![frmFilter_ShortByMonth]![txtDept]));However, there is a second problem in the design of the table and the combo box on the form.
In the table, "Responsible Area" is a TEXT field. However, it should be a Long Integer, and instead of the text string, such as "Controller", it should be the value of the corresponding Primary Key in the Department table. That field is called "ID". It's the same one used in the combo box on the form.
Your criteria is written so that it is using the bound column of the combo box, which is the first column, or Column 1. That is the ID -- a number. However, in the query, it is applied to the text field "Responsible Area", which has the string value "Controller", etc.
You can correct this part of the problem by changing the bound column of the combo box, called "txtDept" from 1 to 2. That's easier than correcting the table design.
I know this part is confusing and my previous answer probably didn't really help.
There are two ways we can refer to the columns in combo and list boxes.
In the FORM's property sheet, the 1st column on the left is column 1. However, in VBA and other places, though, you'd refer to the INDEX of that column, which would be 0.
You'll need to change both of these things.
Hi Tony2021 ,
Alternatively you can use parameters and macros, here is an example I made for you, and tested using your database, it works. I attached the screenshot and the working version of the database.
Here are the steps;
- Modify the row source query of the report in the design mode;
- keep tblAppendPQ.*, remove other criteria
- Use Query parameters to add three parameters pmStartDate, pmEndDate, pmDept, and select corresponding Data Type,
- Close and Save, the query then close and Save the report,
- Create an OpenReport macro,
- refer the dates boxes and the combo box in the parameters section of the macro,
- The tricky part for the department is to use the IIF() function to include or exclude the department for the pmDept parameter. Note that you can always use nested function in a parent function in Access databases (some might have limitations). In your case I used IsNull() inside the IIF.
- Format: IIF( <Condition>, <True part>, <False part>). So, if IsNull returns True then it uses the wildcard (meaning everything), otherwise retrieves the department from the combo.
IIf( IsNull([Forms]![frmFilter_ShortByMonth]![txtDept]) ,'*' ,[Forms]![frmFilter_ShortByMonth]![txtDept] )- name and save it.
- Finally, Call the macro OnClick event of the Report button in the Filter Form.
Also, I think I have a solution for the Receiver, please review the Filter Form I made.
I hope it helps
- Hi Matteo, I hope you are doing well. Not sure if you are following still. If you are I have a quick question. I am trying to add another parameter and I do not see how you did that? thank you very much....
Matteo I like that filter form you made. that is fantastic! I like those radio buttons. I am going to use it. I couldnt get it to filter like the other original form does, which filtered perfectly. The receiver option I actually do not need. I ma not sure if that interferes with anything and if so then it can be deleted.
- "I couldnt get it to filter like the other original form does,..."
That is because the parameters in the CombinedPQ macro still referencing the original form, which needs to be replaced with the sample form I created. Here is the example;
Reference for the original form: [Forms]![frmFilter_ShortByMonth]![txtDateFrom]
Reference for the sample form: [Forms]![frmFilter_ByMonth_Matteo]![txtDateFrom]Hi Mateo,
I have a follow up question when you have a sec. Appreciate your kindness.
I want to add functionality when the date (only the unbound txtDateFrom field) is Null as well as the Dept field then show all records. I tried following your logic for
pmDept:
IIf(IsNull([Forms]![frmFilter_ByMonth_Matteo]![txtDept]),'*',[Forms]![frmFilter_ByMonth_Matteo]![txtDept])following the logic, I put the following in pmStartDate in the macro:
IIf(IsNull([Forms]![frmFilter_ByMonth_Matteo]![txtDateFrom]),'*',[Forms]![frmFilter_ByMonth_Matteo]![txtDateFrom])
but it didnt return any records--OR --
would I modify the [Due Date] field directly in the query:
>=[pmstartDate] And <[pmendDate]+1 (apply IsNull or Nz here?)Thank you very much...
