Question Filter Form Based on Multiple Combo Boxes (1 Viewer)

[MOTOWN44]

Morning

This is probably a simple problem but I cant get my form to filter by multiple criteria. And all I can find on the internet is how to define 1 combo box any another. So any help here would be great.

There's 3 fields called Name, Site, Age which are accompanied by 3 combo boxes called NameFilter SiteFilter and AgeFilter on the same form.

I've been using the simplistic

Me.Filter = "(Site) = '" & Me.SiteFilter & "'"
Me.FilterOn = True

For each combo after update to filter the records on the form (ie no new page open it just displays based on the results on the same form) which thus far has been fine.

Its been decided that we now need to be able to filter in a narrower fashion eg find all the records at a specific site that are a specific age.

The easiest way of describing this would be:

Form shows 100 records
The are criteria entered in the on form filter options SiteFilter and AgeFilter
And there are 20 records showing on the same form with everyone from say London who is 55

Now I have had dealings with NULL before and its been a royal pain in the backside so if at all possible because in the example I used above I have left the Name filter option blank is possible to only filter by the options that are selected?

As in the past leaving a field blank has only brought up results where it would be
Name = “”
Site = London
Age = 55

I basically want the name field discounted because its not filled in and not needed for this filter?

I appreciate I have just rambled on so if you need further clarification please just ask

Thank you in advance

 

[MStef]

I sugges to do this via FILTER BY FORM, CLEAR GRID, APPLY FILTER and REMOVE FILTER icons.

 

[MOTOWN44]

Thanks for the reply

i agree that would be the best port of call but due to the nature of the data we have a company policy of hiding everything (ie toolbars) and locking down the whole database so there is minimal chance of somthing going wrong

for this reason everything must be done via macros, queries, code etc

 

[gemma-the-husky]

doing it with filters is tricky, because of the way you need to build the string.

I assume the combo boxes are unbound.

So, one way of doing this is in the forms query - you can refer to the combo box controls, by putting the value of the controls in the criteria box. However, this still may give you the same problem with managing the nulls, as you get with the filter.


So the other way is to have a function that establishes whether to include this row

in the query have an extra column

includethis(name,site,age), and set the critera to TRUE
(where name site age are the names of the fields in your query/table)


now the function needs to look like this

function includethis(name, site, age) as boolean
end function

within the function you can test the values against the settings on the form, and return a value true/false which determines whether you include the record. Now this method does mean that this function is called for every record in the file is evaluated and tested, so for large files, it can take a few seconds - but it is an easy way of solving your issue.

Hope it makes sense