Multi filter in access 2013

Question

Hi, and thanks in advance for any help on my question.

I have a search form Named QS that is based on a query.

It displays the following fields: 

Name, Building, Floor, Apartment, Gender, Age (age field is created by calling a public function that calculates the age from Date of Birth)

In my search form I have unbounded combo boxes and text boxes for the user to choose options that will be the criteria for multiple filter searches.

The combo boxes are  named cboBuild, cboFloor, cboApt, cboGen

The text boxes are txtFrom, txtTo  which values I use as range for searching by age

Using the combo boxes, the user is able to filter by multiple criterias: for example:

    list of all the women that live in a building in a specific floor. and apartment.

    list of all the men that live in a specific building

    list of all the persons that live in a specific apartment

Using the text boxes, the user can  list of all the persons that are within a range of age.

---

In the AfterUpdate event of the combo boxes I wrote the following code to filter by building, floor, apartment and gender:

DoCmd.ApplyFilter "([Building]=[Forms]![QS]![cboBuild] Or [Building]=[Forms]![QS]![cboBuild] Is Null) And" & _

     "([Floor]=[Forms]![QS]![cboFloor] Or [Floor]=[Forms]![QS]![cboFloor] Is Null) And" & _

     "([Apartment]=[Forms]![QS]![cboRom] Or [Apartment]=[Forms]![QS]![cboRom] Is Null) And" & _

     "([Gender]=[Forms]![QS]![cboGen] Or [Gender] = [Forms]![QS]![cboGen] Is Null)"

---

In the AfterUpdate of txtTo I wrote the code following code to search by age.

Dim filAge as string

filAge = "([Age] >= " & Me.tFra & " And  [Age]<= " & Me.tTil & ")"

DoCmd.ApplyFilter , filAge

Age is a calculated field generated in the query. The calculation of Age is done in a public function from Date of Birth value.

---

Both of the upper filters work well independently.

I tried to combine both filters by adding the Age criteria to the end of the list of the other criterias,  but it doesn't work.

DoCmd.ApplyFilter "([Building]=[Forms]![QS]![cboBuild] Or [Building]=[Forms]![QS]![cboBuild] Is Null) And" & _

     "([Floor]=[Forms]![QS]![cboFloor] Or [Floor]=[Forms]![QS]![cboFloor] Is Null) And" & _

     "([Rom]=[Forms]![QS]![cboRom] Or [Rom]=[Forms]![QS]![cboRom] Is Null) And" & _

     "([Gender]=[Forms]![QS]![cboGen] Or [Gender] = [Forms]![QS]![cboGen] Is Null)" & _

      "([Age] >= " & Me.tFra & " And  [Age]<= " & Me.tTil & ")"

My guess is that it is not working because Age is a calculated field generated from the query.  But I don't if that is the reason, and I dont know how to write the code to add the age filter to the rest of the criteria. 

How to I correct my code for Age to be a part of my multiple filter criterias?

Best regards.

Answer 1

Hi Ken, and thank you very much for your detailed explanation and help.

I used the second suggestion you gave me, of restricting the recordset by means of parameters, (WHERE), and that solved the problem I had.  Now the age filter is integrated and working properly with the other filters.

Many, many thanks again.

Gest regards.

Answer 2

You appear to have a number of unnecessary equality operations in the expression.  Also you are not allowing for the tFra and/or tTil controls being Null.  The following might work:

DoCmd.ApplyFilter _

     "([Building]=[Forms]![QS]![cboBuild] Or [Forms]![QS]![cboBuild] Is Null) And " & _

     "([Floor]=[Forms]![QS]![cboFloor] Or [Forms]![QS]![cboFloor] Is Null) And " & _

     "([Rom]=[Forms]![QS]![cboRom] Or [Forms]![QS]![cboRom] Is Null) And " & _

     "([Gender]=[Forms]![QS]![cboGen] Or [Gender] = [Forms]![QS]![cboGen] Is Null) And " & _

     "(([Age] >= " & Me.tFra & " Or  Me.tFra Is Null) And ([Age]<= " & Me.tTil & "Or Me.tTil Is Null))"

However, you are mixing two different methodologies here.  In the case of the 'Age =' operations you are concatenating the value of the control into the expression, whereas in all other cases you are referencing the controls.  When building an expression like this the former is the better approach, though it is necessary when doing so to take account of the data types of the columns in question and delimit the concatenated values accordingly.

Personally I would do this not by filtering the form, but by restricting its recordset by means of parameters:

     WHERE ([Building]=[Forms]![QS]![cboBuild] OR [Forms]![QS]![cboBuild] IS NULL) AND

     ([Floor]=[Forms]![QS]![cboFloor] OR [Forms]![QS]![cboFloor] IS NULL) AND

     ([Rom]=[Forms]![QS]![cboRom] OR [Forms]![QS]![cboRom] IS NULL) AND

     ([Gender]=[Forms]![QS]![cboGen] OR [Gender] = [Forms]![QS]![cboGen] IS NULL) AND

     ([Age] >= [Forms]![QS]!tFra OR  [Forms]![QS]!tFra IS NULL) AND

     ([Age] <= [Forms]![QS]!tTil OR [Forms]![QS]!tTil IS NULL)

You then merely have to requery the form to apply the restriction.  You'll find examples of this approach  in ComboDemo.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file the option to open a simplified form for drilling down through a hierarchy illustrates the use of a form based on a query which references unbound controls in the form as parameters.   In my case the controls are correlated combo boxes, but that is not relevant to the basic functionality.

Answer 3

Hi Daniel, and thank you for your quick answer.

Yes, it is the concatenation what is failing.  And it is only when I add this particular filter based in a calculated field, generated by my query.  I can add as many more filters and work pristine.

And the Age filter used independently, also works well.  

I just would add the age filter to my other multiple criteria, otherwise I can get the list of all the people that fill the criteria of age, but I can not display for example all the women within that age.

I hope you can tell me what to do to be able to add the Age filter to my other criteria.

Best regards,

Answer 4

Could it be as simple as a missing space?  Try:

DoCmd.ApplyFilter "([Building]=[Forms]![QS]![cboBuild] Or [Building]=[Forms]![QS]![cboBuild] Is Null) And " & _

     "([Floor]=[Forms]![QS]![cboFloor] Or [Floor]=[Forms]![QS]![cboFloor] Is Null) And " & _

     "([Rom]=[Forms]![QS]![cboRom] Or [Rom]=[Forms]![QS]![cboRom] Is Null) And " & _

     "([Gender]=[Forms]![QS]![cboGen] Or [Gender] = [Forms]![QS]![cboGen] Is Null) And " & _

      "([Age] >= " & Me.tFra & " And  [Age]<= " & Me.tTil & ")"

What are the values being used?  Perhaps debug.Print the entire expression and post it back here.

But what i would personally do would be to test each settlement individually to ensure they work and then simply concatenate them together.  Did you already do that and is simply the concatenation that is failing?