Using a Where condition in a Macro

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm using a macro to export the results of a query which is filtered by
building up a filter string (strWhere). Currently the macro exports the
results without applying the filter. What I need to know is how to
incorporate a WHERE clause under the macro's condition field that tells the
macro to filter the query using the strWhere. I admit I don't even know if
this is possible? I have tried using DoCmd.Transferspreadsheet, OutPut To,
etc... I'm having a hard time understanding how to get the filter into the
queries WHERE statement. I have read post after post (and posted a couple
myself) and ask everyones patience as I need a walkthrough here. No matter
how I try the filter does not apply. If someone can help me (even off forum)
I would be very grateful! I would be glad to email the code I have.

Thanks again!
 
One way to approach this is to create a query that you use for the export.
Then create some code that patches the WHERE into the SQL statement, and
exports that.

What you do is switch your query to SQL View. Copy everything before the
WHERE clause as the stub of the SQL statement (strcStub.) Copy anything
after the WHERE clause as the tail if the SQL statement (strcTail.) Save the
query, and put its name into strcQuery.

You already have the code to build the WHERE string (strWhere.) So, you
concatenate the stub, the WHERE clause, and the tail together to make the
SQL statement, and assign it to the SQL property of the query.

You can then export the query, since it has the right WHERE clause in it.

Here's an example of how the code might look:

Private Sub cmdExport_Click()
Dim strWhere As String
Dim strFile As String
Const strcStub = "SELECT * FROM Table1 WHERE "
Const strcTail = " ORDER BY Table1.ID;"
Const strcQuery = "qryExport" 'Name of your query to export here.

strWhere = "(SomeField = 99)"
Currentdb.QueryDefs(strcQuery).SQL = strcStub & strWhere & strcTail

strFile = CurrentProject.Path & "\MyFile.xls"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, strcQuery,
strFile, True
End Sub
 
Thank you Allen. You were very clear. I am finally able to export my filtered
results! (See me jumping up and down!) I think the frustrating part was that
I knew this was a lot easier than I was making it.

Justin
 
Back
Top