Filter a query

[Alexandre]

There you look at the properties of a query in design mode, there is one cqlled filter, thqt I guess must be similar to the reports .filter property. In the case of reports, I know how to programatically change the filter using the DAO .filter or using DoCmd.OpenReport.

Is there also a mean to programatically change the filter property of a query?

 

[jwindon]

Guess:

1) Ask for user input each time?
2) Create a form for the criteria choices and build query each time off of those choices?

If you can open a report based on WHERE, certainly you must be able to do that for queries.

 

[jwindon]

This example may explain my rationale.

Run the code to reset a value on a "dummy" form.

DoCmd.OpenForm "DummyForm"
Echo=True
Me.SetValue.CriteriaControl="Delinquent"
DoCmd.OpenQuery "qryChangingCriteriaNeeded"

The query criteria in Criteria field would be

Where: =Forms![DummyForm]![CriteriaControl]

When the query is run it should show all the records where Criteria has the text "Delinquent" (for example)

Hope you can figure it out from here, but if not, someone else will notice your question and come to BOTH our rescues!

[This message has been edited by jwindon (edited 08-27-2001).]

 

[Alexandre]

jwindon:

Thank you for the proposal, but this is not flexible enough in my concrete case.

What I have at the moment is a form that allows the user to select a query, then builds the WHERE part based on the combination of criterias he/she select(allows relatively complex combinations like: BeginnigDate<#01/01/2001# and ReferenceProject in ("Ref1", "Ref2")).

My point is that in some circumstance, I would need to predefine one or some parameters of the query. For this, it would be great if I could apply the WHERE statement built through the user's choices as a 'supplementary' fitler, instead of replacing the WHERE statment as I am doing at the moment.

Any other ideas?

 

[jwindon]

What about setting your PREDEFINED PARAMETERS and then the USER's CHOICE PARAMETERS under the OR part of the Criteria. I see If..then case in this question now...

Give me an example of a "prefined condition"...If the user selected something...?? Or did not....?

 

[Alexandre]

Thank you jwindon for trying to help.

Well, this makes part of a relatively big system so it is not easy give a synthetic and concrete example. Parameters do not offer the flexibility I want (I want the users to select one among many predefined queries, then to be able to choose the operators for their criteria: IN, BETWEEN, = etc, and for the combinations between criteria AND, OR...). So the solution I took was to dynamically build the WHERE part of my queries based on criteria and operators chosen by the user in a form (very similar procedure to the one used in the Filter by Form process).

The user selects a predefined query, and what I do at the moment is that I change the WHERE part of SQL by the one that was programatically built.
This, because, I do not know any way to filter a query, although I know that when you are in design mode you can access a filter property that is said by the Help to be, once set, load at the same time than the query.

Is this property accessible programatically? How? I know how to do that for forms, reports but not for queries.

Advantage would be that I could easily have predefined queries with predefined criteria, that would be further filtered by users.
To obtain that result at the moment I must combine the predefined and user-defined criteria when building the were clause. It is simple in some cases (WHERE [predefined criteria] AND [user-defined criteria]) but in some other cases it can becom quite complex...

Hope this makes sense.

Alex

[This message has been edited by Alexandre (edited 08-30-2001).]

 

[Alexandre]

I found it.

Just in case anybody would be interested, it seems to be among the very few actions that you cannot be undertaken through VBA, only through macro:

After having opened the query:
DoCmd.ApplyFilter StrQueryName, StrCriteria

Alex