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,
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.
[Forms]![MyForm]![MyComboOrListbox].[Column](1)
Good point. I always forget about using Eval....
Until recently, I've always used a public variable and function to do this with the query criteria set to the function
Hi Colin,
> No - referencing columns doesn't work in a query.
Actually, it works with the help of an old friend:
Eval("Forms!frmFilter_ShortByMonth!txtDept.Column(1)")
Servus
Karl
*********
http://AccessDevCon.com
https://www.donkarl.comNo - referencing columns doesn't work in a query.
Perfectly happy for you to add detailed advice...however that code could be simplified to just one line:
TempVars.Add "Department", [Forms]![frmFilter_ShortByMonth]![txtDept].Column(1)Then use TempVars!Department in the query filter criteria
For more info on using TempVars, see
https://accessexperts.com/blog/2010/09/16/maximize-the-user-of-tempvars-in-access-2007-and-2010/- That reference works perfect for a textbox, so I assumed that it will also work within a query, I didn't test, you could be right.
In addition, Tony2021 said "I am not a coder" in his first post, so I didn't want to make it more complicated for him, even macros are new to him.
On the other hand, I agree with you, the bottom line is we're looking at a three lines of short codes. Some easy syntax could be good start for him. If you don't mind I would like to interpret your advise;
Your #2-A Declaration of public variable under module:
Public Department as TempVars
Your #2-B Criteria for the [Responsible Area] in the query :
[TempVars]![Department]
Your #1 AfterUpdate event of the combo, sets the value to the tempvar:
TempVars!Department = [Forms]![frmFilter_ShortByMonth]![txtDept].Column(1) If I recall correctly, you cannot reference combo/listbox column numbers in query criteria.
Instead do one of the following:
1. Create a public variable and set this equal to e.g. [Forms]![MyForm]![MyComboOrListbox].[Column](1).
Next create a public function to recall that value and place the function in the query criteria
2. Create a TempVar and set it to the combo column value then reference the TempVar in the query criteria
