Opening a query with a filter in Access 2007

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carl Rapson - 31 May 2007 21:10 GMT

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

Reply to this Message

Allen Browne - 01 Jun 2007 03:54 GMT

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 ...

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

> 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
[quoted text clipped - 7 lines]
>
> Carl Rapson

Reply to this Message

Carl Rapson - 01 Jun 2007 16:03 GMT

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

>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.
[quoted text clipped - 21 lines]
>>
>> Carl Rapson

Reply to this Message