SOLVED

Filter form

Steel Contributor

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

 

 

19 Replies

@Tony2021 

 

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.

@George Hepworth 

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. 

 

best response confirmed by Tony2021 (Steel Contributor)
Solution

@Tony2021 

 

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.

@Tony2021 

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 @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,
    • msb01-02.JPG
  • 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.

msb01-01.JPG

 

  • 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

 

@George Hepworth 

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.  

Tony2021_0-1631224490350.png

 

 

 

@Matteo  wow that is crafty.  I think I get it.  Little out of my league though.   thank you very much for your insight.  

@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. 

Unfortunately Access' intelisense does not show it, but type to refer the control in following format it should work;
[Forms]![MyForm]![MyComboOrListbox].[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

"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]
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)

@Matteo 

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 
https://accessexperts.com/blog/2010/09/16/maximize-the-user-of-tempvars-in-access-2007-and-2010/ 

 

 


 

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.com

 

@Karl Donaubauer 

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

@Matteo 

Hi Mateo,

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
pmDept:
IIf(IsNull([Forms]![frmFilter_ByMonth_Matteo]![txtDept]),'*',[Forms]![frmFilter_ByMonth_Matteo]![txtDept])

following the logic, I put the following in pmStartDate in the macro:
IIf(IsNull([Forms]![frmFilter_ByMonth_Matteo]![txtDateFrom]),'*',[Forms]![frmFilter_ByMonth_Matteo]![txtDateFrom])
but it didnt return any records

Tony2021_0-1631398123517.png

--OR --
would I modify the [Due Date] field directly in the query:
>=[pmstartDate] And <[pmendDate]+1 (apply IsNull or Nz here?)

 

Thank you very much...

 

 

Hi @Tony2021 

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;

DateSerial(Year(Date())-2,1,1) 

(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. 

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....
1 best response

Accepted Solutions
best response confirmed by Tony2021 (Steel Contributor)
Solution

@Tony2021 

 

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.

 

View solution in original post