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 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.
While you've found a solution that works, I'm not convinced it's the optimal one.
Using a textbox control means the user has to type in one of the Department names, and that can lead to typos which interfere with matching. Your original approach using a combo box alleviates that problem.
The issue is which column in the combo box is used to do the matching.
The combo box has two columns, one for the ID (number) and one for the Department name (string).
Your table has a text field for Department (which as I suggested should have been the ID relating to the Department in the Department table). While that's not the ideal table design, it can work.
What your query should do is match the ID to an ID (which it can't because of the table design), OR it should match the string value for Department to the string value for Department.Back to the combo. I got you off on the wrong track by talking about the column indexes. They don't matter here. Sorry. The would matter in other situations, though.
What matters here is the bound column. If the bound column is column 1, i.e. the first column on the left, then it will reflect the ID (number). If the bound column is column 2, i.e. the second column, then it will reflect the Department name (string). Simply changing the bound column from 1 to 2 would then allow your query to match a string to a string. Plus, because it's in a combo box with the values pre-populated, there's no need to ask a user to type in a Department name, and there's no chance of a misspelling. The values are set and can be counted on to match the filter in the query.
Again, apologies for making this more complicated than it needed to be.
Hi George,
Yes, I would rather use a combo box but how would I reference it in the query? I thought I could put .column(1) but there is no property for this.