set filter property in query from vba

[Beantree]

Anyone know how to set the query's Filter and Orderby properties from VBA?

I am getting user defined filter and sorting info from a form which gives the user the option to launch either the report or the query. I can pass the filter and orderby to the report.

In order to get the query to work, I had to make a form in datasheet mode so that I can pass the filter and orderby to it.

I see in the query's properties that a filter and orderby property exist, but can't find how to set them.

I don't want to redefine the where and Orderby clause of the SQL itself, mainly because the where may have other criteria the user isn't allowed to change, and I don't want to mess with it.


I also notice that the report runs relatively quickly (30 secs) but the query and the form based on the query take over twice as long. The query is simple, a link to an Oracle view. Both the form and the report are based on the same query. The report is not summarizing or grouping at all.

Any ideas?

 

[DougP]

When you use VBA your Queries are slightly different.

So it would look like this.
Dim db as database, rst as recordset, SQL as string
Set db = CurrentDb
' SQL string.
SQL = "SELECT * FROM Orders WHERE OrderDate >= #1-1-95#; Order by Number DESC"
Set rst = db.OpenRecordset(SQL)

Which make sort order of the Number field Descending
Leave off the word DESC completley and it would be Ascending
DougP, MCP

Visit my WEB site

http://www.barcodeone.com

to see how Bar-codes can help you be more productive

 

[Beantree]

I understand how to write sql statements in VBA. What I asked was how to set the filter and orderby properties of a saved query.

 

[LarryDeLaruelle]

Beantree:

Sounds like you are doing a query by form.

If so, use the expression builder to reference the fields on your form that contain the criteria. (right click in the criteria cell and select Builder.)

Hope this is what you need.
Larry De Laruelle
larry1de@yahoo.com

 

[Hmadyson]

First off, what are you doing with the query, are you cycling through the records or are you displaying them like a form?

If you are cycling through the records then to filter you would have to use the openrecordset to create a recordset of the data, then apply the filter of the recordset and then create a new recordset after the filter is set. I am not sure about ordering.

If you are trying to display the query, then maybe you should attach it to a form and then use the filter and orderby properties of the filter. Just make sure to set filteron=true and orderbyon = true.

I hope this helped.

 

[Beantree]

I am displaying the query results to the screen as a datasheet, using the docmd.openquery method.

I am currently using a form so that I can use the Filter and orderby properties.

My question is how do I set the filter and orderby properties for a query from VBA, so I can just launch the query and not have to design a form for each one I want to have this available for? If you go to any query's properties, you will see that the filter and orderby are available properties. I just want to know how to set them from VBA.

 

[waldemar]

Still need help?