How to export filtered query results ONLY

[Justin83716]

I have an unbound main form (frmSearchDatabase) with a number of criteria and
a subform (fsubRecordSearch). The control source for the subform is a query
(qrySearchDatabase). The criteria is used to filter the query. What I need to
do is export the filtered results to Excel. I have created a button on the
mainform (cmdExcelExport) with a On Click event that links to a macro
(mcrExportExcel). The macro simply uses the OutputTo action and output format
of Excel.

My problem is that it does not export the filtered results of the query, but
the entire query. Or rather it exports the query as though no criteria was
selected, which unfortunatly results in ALL records being returned. (which is
what I want). How do I get the macro to ONLY EXPORT THE RESULTS OF THE
FILTERED QUERY?

Thanks!

 

[Stefan Hoffmann]

hi Justin,

I have an unbound main form (frmSearchDatabase) with a number of criteria and
a subform (fsubRecordSearch). The control source for the subform is a query
(qrySearchDatabase). The criteria is used to filter the query. What I need to
do is export the filtered results to Excel. I have created a button on the
mainform (cmdExcelExport) with a On Click event that links to a macro
(mcrExportExcel). The macro simply uses the OutputTo action and output format
of Excel.

Don't use a macro, do some coding. Use the DoCmd.TransferSpreadsheet method.


mfG
--> stefan <--

 

[Justin83716]

Stefan,
Thanks for responding. I have also tried DoCmd.TransferSpreadsheet, but
again it returns ALL records not the filtered results. I'm not sure why this
is not working? At this point I have the following coding?

Private Sub cmdExcelExport_Click()
**ALL ONE LINE** DoCmd.TransferSpreadsheet acExport,
acSpreadsheetTypeExcel9, "qryRecordSearch", "C:\Documents and
Settings\vandj001\Desktop\SearchResults.xls"
End Sub

My search query (qrySearchDatabase) returns ALL records and by selecting
the criteria on my main form, I narrow down the results. I think my problem
lies in the fact that when I export it takes the query (qrySearchDatabase) AS
IS without the filter applied. How do I make sure that the filter gets
applied to the query BEFORE it exports?

 

[Stefan Hoffmann]

hi Justin,

Thanks for responding. I have also tried DoCmd.TransferSpreadsheet, but
again it returns ALL records not the filtered results. I'm not sure why this
is not working? At this point I have the following coding?

This was not a good advice at all. The problem is, how to get your
filter information.

You can export

"SELECT * FROM qryRecordSearch WHERE " & Filter


mfG
--> stefan <--