Query Filters

[x0reset]

If you view Query properties in design mode you will see a "Filter" property. When I put a WHERE statement in this section and click the "Apply Filter" button it works exactly as I anticipated, filtering out the records I want.

Does anyone know the syntax to change this property in VBA? Specifically, I want to add a command button to my reports switchboard that will apply a filter to a query.

Thanks in advance!

 

[tkpstock]

Is the purpose of this to filter the results returned in the report? If so, check out

DoCmd.OpenReport

You can specify a filter when you open the report.

Normally, a query is a filter in its own right - you can query for the records you want - the filter is the WHERE part of the query (or the Criteria).

HTH

 

[x0reset]

Thanks for the reply Tom.

Unfortunately, my report is so complicated I can't include the criteria fields in the data set of the report. I'm counting yes/no data fields in the main report (Totals Query in the data source), then summing them in the grouping footers. Adding the criteria fields I wish to use creates extra "Group By" parameters that screws up my counts.

I eventually did find a way to get the totals I want while including the necessary criteria in the report recordset (it took some coding in the "On Print" events of my section headers and footers). Unfortunately, I can't add a grouping level I want to use to order the report without running into the same problem.

So at the moment I am stuck using the lesser of two evils: I can filter by the Criteria I want, but I can't order by the field I want to. If I want to get it working perfectly, I have to use two queries: one to filter the data in the source query, and one to count the totals in the report recordset. Passing filter information to the source query was the most elegant solution I could find, but I can't find a way to pass filter information to the query in the "On Click" event of the button on my report switchboard.

Sticky problem.

 

[tkpstock]

Here is a non-elegant solution:

Create a global variable(s) which you can set at the on-click event.

Create a custom function to return the value of the variable(s)

Use that custom function in the criteria of your base query.

HTH

 

[x0reset]

Hey! Good idea! Why didn't i think of that?

 

[tkpstock]

There are probably more elegant solutions, but I just happened to be full of the non-elegant ones...

Glad I could help!