Opening a query with a filter in Access 2007

[Carl Rapson]

Since under Access 2007 reports can no longer be exported to Excel, I'm
attempting to open the query that the report is based on so that I can
export the query to Excel. I'm wondering, though, about the best way to open
the query since I need to apply a filter. It was easy enough to do in the
OpenReport call by using the WhereCondition parameter, but OpenQuery has no
such parameter. What is the best way to filter a query through code?

Thanks,

Carl Rapson

 

[Allen Browne]

A fairly simple workaround is to save the query you want to use for
exporting. Then write its SQL property before you do the export.

The idea is to switch the query to SQL View, and spit it into 2 strings so
you can patch the WHERE clause between them. This kind of thing:

Dim strWhere As String
Const strcStub = "SELECT * FROM Table1 WHERE ("
Const strcTail = ") ORDER BY Table1.ID;"

strWhere = "SomeField = 99"
Currentdb.QueryDefs("Query1").SQL = strcStub & strWhere & strcTail
DoCmd.TransferSpreadsheet ...

 

[Carl Rapson]

Thanks Allen, I'll take a look at that. I guess I'm going to have to get
used to working more with Queries in Access 2007. Reports seem to have been
"dumbed down" in some ways.

Carl Rapson