How to export filtered query results ONLY

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Justin83716 - 12 Mar 2007 21:21 GMT

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!

Reply to this Message

Stefan Hoffmann - 13 Mar 2007 11:05 GMT

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
[quoted text clipped - 3 lines]
> (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 <--

Reply to this Message

Justin83716 - 14 Mar 2007 22:49 GMT

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?

> hi Justin,
>
[quoted text clipped - 9 lines]
> mfG
> --> stefan <--

Reply to this Message

Stefan Hoffmann - 15 Mar 2007 11:02 GMT

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

Reply to this Message