Sep 05 2021 04:18 PM
Sep 05 2021 04:18 PM
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
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.
Sep 05 2021 05:33 PM - edited Sep 05 2021 07:46 PM
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
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.
Sep 06 2021 10:04 AM - edited Sep 06 2021 10:05 AM
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.
Sep 06 2021 03:27 PM - edited Sep 06 2021 03:31 PMSolution
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]
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.
Sep 06 2021 07:20 PM
Sep 07 2021 07:08 AM
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.
Sep 08 2021 09:41 AM
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;
IIf( IsNull([Forms]![frmFilter_ShortByMonth]![txtDept]) ,'*' ,[Forms]![frmFilter_ShortByMonth]![txtDept] )
Also, I think I have a solution for the Receiver, please review the Filter Form I made.
I hope it helps
Sep 09 2021 03:27 PM - edited Sep 09 2021 03:34 PM
@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.
Sep 09 2021 04:00 PM
Sep 10 2021 12:13 PM - edited Sep 10 2021 03:34 PM
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
Sep 10 2021 08:40 PM
Sep 10 2021 09:57 PM
Sep 11 2021 01:42 PM - edited Sep 11 2021 01:44 PM
No - 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
Sep 11 2021 02:14 PM - edited Sep 11 2021 02:15 PM
Sep 11 2021 03:09 PM
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
following the logic, I put the following in pmStartDate in the macro:
but it didnt return any records
would I modify the [Due Date] field directly in the query:
>=[pmstartDate] And <[pmendDate]+1 (apply IsNull or Nz here?)
Thank you very much...
Sep 13 2021 08:09 PM - edited Sep 13 2021 08:11 PM
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;
(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 If
I hope that helps.
Nov 13 2021 06:16 PM