Export to Excel using dynamic WHERE clause

[J.Griffis]

Current application prompts user for desired WHERE criteria, which is then
used to create a valid WHERE clause string.

Neither DoCmd.TransferText nor DoCmd.OutputTo allow filtering or other WHERE
clauses, nor can I pass a filtered recordset to either of these methods.

What would be the most reasonable and effective way to export the filtered
query to a text file?

Thanks in advance,
-Jason

 

[Klatuu]

You can use a query to to the export instead of a table. Now the dynamic
part is what is fun. Create your query with no critera. Then when you have
your where string built, add it to the query's SQL, then do your transfer.

Dim qdf as QueryDef
Set qdf = CurrentDb.QueryDefs("MyQueryName")
'Sample Where Clause
strWhere = "WHERE tblGoofy.mickey = 'Donald Duck';"
'Read the SQL string from the saved query
strSQL = qdf.SQL
'Save the original. We need to put it back so our code will work next time
strOriginalSQL = strSQL
'Adds the Where Clause
strSQL = Replace(strSQL,";",strWhere)
'Write it back out the the query
qdf.SQL = strSQL
'Do the Transfer
DoCmd.TransferText........
'Now put the original SQL back
qdf.SQL = strOriginalSQL

set qdf = Nothing

 

[J.Griffis]

Cool, thanks. I had been using ADO elsewhere in the application and hadn't
looked at DAO. Thanks for the reminder and the code below...works like a
charm!

Jason

 

[Bonnie]

Hi Klatuu, hope you still have a thread attached here. I came across the
info below and am excited about trying it out. One question: My goal would
be to have 'Donald Duck' be the value of a field on my form. Your wordage
reads:

'Sample Where Clause
strWhere = "WHERE tblGoofy.mickey = 'Donald Duck';"

How would I write the following: [Forms]![fCensus1Conversion]![RunThisOne]
into the statement so the WHERE would be the value of my form field? The
quotes mess me up every time.

Thanks in advance and hoping to hear back...