Forum Discussion
Filter form
Hello, I have a filter form with filters to be applied to a report on open.
I want to be able to filter the report based on what I choose in cboDept.
cboDept is unbound with has a rowsource query, bound column 1:
SELECT tblDept.ID, tblDept.Dept FROM tblDept ORDER BY tblDept.Dept;
On the field Dept in the report record source query I am referencing like this:
=[forms]![frmFilter_ShortByMonth]![cboDept] this is the filter form
I dont get any hits but I should
I think it has something to do with
=[forms]![frmFilter_ShortByMonth]![cboDept]
as it might be searching for text
I thought I needed
=[forms]![frmFilter_ShortByMonth]![cboDept].column(1) but that gives me an error.
do you see something wrong? I am not a coder by the way.
thank you
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.
19 Replies
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]
- Modify the row source query of the report in the design mode;
Columns in combo and list boxes are Indexed, left to right, starting with 0.
That means Column(0) is the FIRST column.
The tricky part is that the form refers to the Bound Column by position. That means, if your combo box designates Column 1 as the bound column, you have to refer to it as Column(0).
=[forms]![frmFilter_ShortByMonth]![cboDept] would refer to the bound column by default, so that should pick up the first column, which is tblDept.ID, according to the SQL posted.
Or, you could refer to it as
=[forms]![frmFilter_ShortByMonth]![cboDept].Column(0)
That's all pretty much what you are describing except for the issue of which column you need to specify if you want to include it.
To see more clearly what's going on, we'd have to look at the recordsource for the report itself and HOW and where you go about applying the filter to it.
Hi George, sorry for my tardy reply. It is probably easier to take a peek at the attached db. The form opens auto and select from "today to end of next quarter" and then select from the cbo "controller" and the report does not filter. The column (1) property is not an option in the query meaning that I can not assign [forms]![frmFilter_ShortByMonth]![txtDept].column(1)
Do you see what I am doing wrong? Grateful for your help.
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.
