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

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

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

"Carl Rapson" <mr.mxyzptlk@newsgroups.nospam> wrote in message
news:%23w8Ma$7oHHA.3368@TK2MSFTNGP02.phx.gbl...
> 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 

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

"Allen Browne" <AllenBrowne@SeeSig.Invalid> wrote in message 
news:emtmxg$oHHA.588@TK2MSFTNGP06.phx.gbl...
>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 ...
>
> -- 
> 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.
>
> "Carl Rapson" <mr.mxyzptlk@newsgroups.nospam> wrote in message
> news:%23w8Ma$7oHHA.3368@TK2MSFTNGP02.phx.gbl...
>> 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
>