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
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...
Hi Tony2021
The "*" wildcard is corresponds multiple values. Remember the pmStartDate... is a parameter, designed to be used in a range criteria for a specific date. You cannot replace one side of a range with multiple values; instead, range (criteria) must have a start and an end date, otherwise you need to replace the whole criteria with another criteria. For example, >= [pmStartDate] only, meaning no end date, or <[pmendDate]+1 only, meaning no start date.
I'd suggest replacing the wildcards with a standard and dynamic start date. For example; if the data is 10 years old, you might want to see the data starting from 2 years ago when the Date From control left empty; meaning, start date of your date range cannot be older than 2 years;
So make the True part of the IIF as;DateSerial(Year(Date())-2,1,1)
(Now it's 01/01/2019 next year it will be 01/01/2020 )
IIf( IsNull([Forms]![frmFilter_ByMonth_Matteo]![txtDateFrom]), DateSerial(Year(Date())-2,1,1) ,[Forms]![frmFilter_ByMonth_Matteo]![txtDateFrom])Note: Search the the internet for following dynamic dates; beginning of this quarter, Beginning of this week, first day of this month and so on...
Alternatively you can handle empty control issue in the AfterUpdate event of the option group; before the "Select Case..." instead of dealing with the macro;
If Len(Me!txtdatefrom.Value) = 0 Then Me!txtdatefrom = DateSerial(Year(Date) - 2, 1, 1) End IfI hope that helps.