export filtered records to excel

OceanCity
OceanCity used Ask the Experts™
on
Hi,

I created a form (based on a query) whereby users select from a dropdown, answer to date prompt (begin/end dates) and click “run query’ button to pass those parameters to the report.  If users do not answer to a prompt, and/or do not make any selections, the report displays everything.  If users made some selections, then the report reflects selections made and only shows filtered data.  So far, this works beautifully.  

I then created another command button to exports the filtered data to excel.  Following is the code I used:

Private Sub cmdExport_Click()

DoCmd.OutputTo acOutputQuery, "qry_MyQueryName", acFormatXLSX, , True

End Sub

But, the report gives me everything (it doesn’t reflect the filtered data)

Can you please assist?

TIA

Regards,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
NorieAnalyst Assistant

Commented:
Is 'qry_MyQueryName' the name of the query the report is based on?
Distinguished Expert 2017

Commented:
Are the arguments in a WHERE clause in qry_MyQueryName?

Filtering the report either with the filter argument or where argument in the OpenReport method doesn't alter the query.
OceanCityData Analyst

Author

Commented:
Thanks both for the reply post.
Yes, the report is based on qry_MyQuryName

What I’m trying to accomplish is very similar to the following discussed in this forum (except that I use office 2016):

Export access 2010 form records to Excel
https://www.experts-exchange.com/questions/28324734/Export-access-2010-form-records-to-Excel.html


TIA,
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Distinguished Expert 2017

Commented:
There were two questions on the table.  Just because the report uses the query as its recordsource is irrelevant UNLESS the criteria is IN the query as a WHERE clause.
OceanCityData Analyst

Author

Commented:
Pat,
If I understand your question correct, the criteria in the query is as a WHERE clause.  
I’ve attached the code, please let me know if you have any questions.

 TIA

Regards,
Code.txt
Top Expert 2009

Commented:
I like to save a filtered query, which can then be used for export to Excel (or many other things).  Here is some sample code, using this technique to create filtered reports:
Public Sub CreateFilteredReports()
'Created by Helen Feddema 25-Aug-2010
'Last modified by Helen Feddema 25-Aug-2010

On Error GoTo ErrorHandler

   Dim lngCount As Long
   Dim strQuery As String
   Dim strRecordSource As String
   Dim strReport As String
   Dim strSQL As String
   Dim strCustomerID As String
   Dim rstCustomers As DAO.Recordset
   
   strRecordSource = "tblOrders"
   strQuery = "qrySelectedCustomerOrders"
   Set dbs = CurrentDb
   Set rstCustomers = dbs.OpenRecordset("tblCustomers")
   strReport = "rptSingleCustomerOrders"
   
   With rstCustomers
      Do While Not .EOF
         strCustomerID = ![CustomerId]
         Debug.Print "Processing Customer ID " & strCustomerID
         
         'Create filtered query
         strSQL = "SELECT * FROM " & strRecordSource & " WHERE " _
            & "[CustomerID] = " & Chr(39) & strCustomerID & Chr(39) & ";"
         Debug.Print "SQL for " & strQuery & ": " & strSQL
         lngCount = CreateAndTestQuery(strQuery, strSQL)
         Debug.Print "No. of items found: " & lngCount
         
         If lngCount = 0 Then
            GoTo NextCustomer
         Else
            'Print report for this customer; the report has qrySelectedCustomerOrders
            'as its record source
            DoCmd.OpenReport ReportName:=strReport, View:=acViewNormal
         End If
      
NextCustomer:
         .MoveNext
      Loop
   End With
   
ErrorHandlerExit:
   Exit Sub

ErrorHandler:
   MsgBox "Error No: " & Err.Number _
      & " in CreateFilteredReports procedure; " _
      & "Description: " & Err.Description
   Resume ErrorHandlerExit

End Sub

Open in new window


And here is the CreateAndTestQuery procedure:
Public Function CreateAndTestQuery(strTestQuery As String, _
   strTestSQL As String) As Long
'Created by Helen Feddema 28-Jul-2002
'Last modified 11-May-2013

On Error Resume Next
   
   Dim qdf As DAO.QueryDef
   Dim rst As DAO.Recordset
   
   'Delete old query
   CurrentDb.QueryDefs.Delete strTestQuery

On Error GoTo ErrorHandler
   
   'Create new query
   Set qdf = CurrentDb.CreateQueryDef(strTestQuery, strTestSQL)
   
   'Test whether there are any records
   Set rst = CurrentDb.OpenRecordset(strTestQuery)
   With rst
      .MoveFirst
      .MoveLast
      CreateAndTestQuery = .RecordCount
   End With
   
ErrorHandlerExit:
   Exit Function

ErrorHandler:
   If Err.Number = 3021 Then
      CreateAndTestQuery = 0
      Resume ErrorHandlerExit
   Else
   MsgBox "Error No: " & Err.Number _
      & " in CreateAndTestQuery procedure; " _
      & "Description: " & Err.Description
   End If
   
End Function

Open in new window

Distinguished Expert 2017

Commented:
OceanCity, the code creates a WHERE argument for the OpenReport method but the WHERE clause is NOT IN THE QUERY so exporting the query doesn't pick up your VBA  where clause.  The query is not connected in any way to the WHERE clause you are building in your code.   Helen has given you examples that should help.
OceanCityData Analyst

Author

Commented:
Helen,

Thank you for the reply post and examples. I’m relatively new to vba and wanted to know:
1. Where exactly is the filtered query saved? In other words, do I place the code in the click event or export ‘report’ button?
2. In my case, my query is based on multiple tables.  Based on your example strRecordSource = "tblOrders" do I need to create the same for each of my tables?

TIA

Regards,

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial