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.
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.
- HI George. thank you for your response. I think I follow what you are saying. What I did instead was removed the cbo on the form and used an unbound box and I manually typed in the dept and used a "like" statement in the query in the report. It seems to be working. Thank you for the tips on the queries. i was wondering about how the OR worked. I have not used Access in quite some time so I am pretty rusty. Thank you for the refresher.
