Link to home
Start Free TrialLog in
Avatar of Member_2_1316035

asked on

export filtered record source of a report

I have a reporting interface for users to create filtered reports.  I need to find the best way to export the data in the filtered reports to excel.

An unbound form contains 5 combo boxes that a user can select to filter a report.  The filtered report is generated with the on-click event of a button on the form.

       DoCmd.OpenReport "rpt_Admits", acViewReport, , strWhere

The report opens with the correct filters applied and the user can click a button on the report header to "print report".

The users want to be able to export the filtered data to excel, so I need to add another button to the report header to "export data".  

As a temporary solution, I am using this code to export the report in excel;

    DoCmd.OutputTo acOutputReport, "rpt_Activity", acFormatXLS, , True

It works fine and gets the job done, but it's not great.  Since my report has several grouping levels, exporting the report is not an ideal way to provide users spreadsheet of data for them to manipulate.  Instead, I would like to export the filtered record source that was used to generate the report.

Can you advise me on the best way to do this?  From my report, can I access the 'strWhere' variable I used on the previous unbound form?  Or is there a better way to access the filtered record source?

Thanks for your help.
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Then it may be easier to run the report from a query with parameters, then you can just export the query.

See this sample
Avatar of Member_2_1316035


Does this method allow for cases where no criteria is selected?  In other words,  a user can use any combination of the available 5 combo boxes to set criteria for their report.  Any of them can be null.  I seem to recall I tried this method once but had difficulty with nulls.
<Does this method allow for cases where no criteria is selected?>
That is a WHOLE different topic.

<I seem to recall I tried this method once but had difficulty with nulls. >
Yes, ...this will add yet another level of complexity to the existing design.
...and it may not be easy to code, you will have to allow for multiple fields, date ranges, Text and Number criteria, Nesting levels, ....and possibly And/Or Logic...

My sample here addresses your question as posted.

If you want to allow "Show all if Criteria box is left empty" (or alternatively "Show nothing if Criteria box is left empty") logic for all the criteria fields, then you may be better off building a query and using syntax like this for each of the criteria fields:

ProductID=Forms!YourForm!txtProductID Or FormsYourForm!txtProductID Is Null
(Here if the Product ID textbox on the criteria form is left blank, all records are returned.

But again, this is a whole other topic, ...and to be addressees properly, it would be best to post a new question on this topic.


In the unbound form, the users need to have the flexibility to choose which if any/all of the 5 boxes they want to use to filter.  I string this together into a 'strWhere' variable which I use to generate my report.  This works fine.  

I'm just wondering if after the filtered report is open if I am able to use that same 'strWhere' clause that was created on the previous form to export the data to excel.
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
But another Expert may be along with a more efficient approach...
Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
My other thought was to build the entire recordsource in code.
Then you could feed that to the report, and to a saved QueryDef....

But I wanted to use your existing strWhere...
Thanks for both of your help.  I am going to try and implement boag2000 solution first.  I agree, it is a bit of trouble, but the users need an interface to build custom reports and export data...

I'll post back soon with my progress.
For simplicity, I personally would go with the query approach...

So take your time and try all of our suggestions.

Then let us know...


Simple is good : )  When you say "query approach", do you mean your first solution of building the temp query?
My very first post where you dont even use  strWHERE

You build a query that looks at the form for the criteria...
Just checking in to let you know that due to user requirements, I had to proceed with implementing the query definition approach.  Yes, it is a bit of work but it seemed like the only way to provide them an interface to run the types of reports they require.  The users needed a form that allows them to select which filters they want to use (or none at all) - so it had to support the use of null values in the combobox.  

I have completed the coding for one of the more simpler queries and it seems to have worked well!   Jeff - thanks for your excellent example!  I am hoping to get through the rest this afternoon and finish testing before closing this question.  

The only downside I am able to note is that the QueryDef is created every time the user runs the report, instead of only when they select to export it. This seems to have slowed the creation of the report a bit.  But the benefit of having the "export records" button inside the report seems to be worth it since it has given them the requirements they asked for.  Essentially, they wanted to be able to filter a report, view the results and then decide from that screen whether they wanted to print or export.
Ultimately, you can run the report normally (as you did before) and store the "strWhere" as a variable.
Then only call up the QueryDef Code if they click the "Export Report Data" button.

Up to you really...
As I stated, there are a few way that this could be done, depending on all the factors involved...

The "export Report Data" button is inside the header of the report.  

Would I have to change strWhere to a global variable in order to access it from the report?
<Would I have to change strWhere to a global variable in order to access it from the report? >
Depends of where you are building strWhere...

It looks like you are passing it to the report as the Open Args.
If so, then you can grab it from there...

strQuery="SELECT * FROM YourTable WHERE " & me.openargs
As I explained in http:#a38158376 you don't need to make strWhere global, store it, or pass it as open arguments. The report already has it in the Report.Filter property. The linked article shows how the report can use both the actual filter and a human readable version of the filter...

sorry h,

I lost track of who said what first
(or even what I said)....
No problem (cute ASCII art!) — (^v°)
Harfang:  I finished reading through your articles - very nice documentation on the use of building custom filters.  Well done!  I especially like what you've done with multi-select and option groups.  It's given me ideas to improve upon the criteria form I created, later.  

I have never used this method before, so I have a couple questions on the implementation.

So I have already successfully built my criteria in strWhere and passed it to the report filter. So conceptually speaking, after I open my filtered report the on-click event of the export button (which is in the report header) will build the strSQL as you suggested:

strSQL _
        = " SELECT *" _
        & " FROM (" & Reports(0).RecordSource & ")" _
        & " WHERE " & Reports(0).Filter

How do I access the strSQL to export to excel?  OutputTo requires an object name, correct?  
DoCmd.OutputTo acOutputQuery, strQueryName, acFormatXLS

I really appreciate both of your help on this topic and have enjoyed exploring each way to solve my problem.  

At first glance, I like this method from the perspective that I can reference the report recordsource (the query) instead of having to build the entire query string SQL.  It seems a bit better from a maintenance perspective - in the event that I need to change the query of my report.  Plus my queries are rather complex with nested Iif statements and sub-queries it is challenge to get the syntax correct with the single and double quotes and all the concatenation.

Many thanks.
Thank you for the feedback, and I'm glad you liked the articles. Make sure to use the vote buttons (“Was this article helpful?”) when you do.

You do need an object for the DoCmd methods, but that's easy to accomplish. Create a query for that purpose, and write its SQL dynamically. For example, create “ztqryExport” (use whatever name suits your naming convention) with any valid query (even just “SELECT 1;”) and use:
    CurrentDb("ztqryExport").SQL = strSQL
    DoCmd.OutputTo acOutputQuery, "ztqryExport", acFormatXLS

Open in new window

If you go that route, bear in mind that simply enclosing the report's record source in brackets doesn't work as such. You will need at least to remove the ending semi-colon that the query assistant probably added there for no good reason I can think of. Since I suggested it, I had to try and came up with this prototype:

Sub QueryFromReport()
    Const THE_REPORT As String = "rptOrders"
    Const EXPORT_QRY As String = "ztqryExport"
    Dim rpt As Report
    Dim strSQL As String
    strSQL = "CustomerID = 123"
    DoCmd.OpenReport THE_REPORT, acViewPreview, _
    Set rpt = Reports(THE_REPORT)
    strSQL = rpt.RecordSource
    strSQL = Trim(strSQL)
    If Right(strSQL, 1) = ";" Then _
        strSQL = Left(strSQL, Len(strSQL) - 1)
    If rpt.FilterOn And Len(rpt.Filter) Then
        strSQL _
            = " SELECT *" _
            & " FROM (" & strSQL & ")" _
            & " WHERE " & rpt.Filter
    End If
    CurrentDb.QueryDefs(EXPORT_QRY).SQL = strSQL
    DoCmd.OpenQuery EXPORT_QRY
End Sub

Open in new window

It opens a filtered report, and then uses the report's source and filter to write a valid query and open it. This will work for a clean record source, one where every field has a distinct name, using aliases if needed.

Good luck!
Harfang:  Thanks for the example.  I will work on this and keep you posted of my progress.  I'm temporarily diverted to another issue, so it may be a day or so before I can respond.
I think that you can see that harfang's approach is ultimately similar to mine (build a saved query.)

His is a bit more refined, so I have no issue with you going with his post.

FWIW, this question comes up every-once in a while, and I too wish there was an easier way to do this...

Something like this:
Docmd.OutputTo "YourReport",acFormatXLS,"C:\Folder\YourFile.xls", ExportReportDataOnlyAsFiltered=True


Yes, I definitely see the similarities in approach.  I am actually working on combining a bit of each of your suggestions.

I completely agree that it is an unfortunate flaw that Access does not give a more graceful reporting interface for end users.
Well the reporting is great, but getting it to do everything you might want is the tricky bit...

The "OutputTo" command (using a report as the source) will output the filtered data, it will also try to bring along the formatting...
(so another option might be to create a "Simple" report with the same Recordsource..., and export that when the need arises)
(But this is another topic)

Using or TransferSpreadheet will export the raw data, but it will only accept a table or query as the source.
(That is why my first suggestion was jut to use a parameter query as the recordsource.)

But as harfang will tell you, most experts here try not to say that a certain solution is "Better", ...most will just explain the differences.

Besides, many of us are so busy, that there is really no time to split hairs...

   "As Long as it works for you"


I was just answering your call for “another Expert” almost a week ago! <*grin*>


« combining a bit of each of your suggestions. »

That's the best way. You will end up with something you like, understand, and are able to maintain. Feel free to show us what you got if you run into problems.

<I was just answering your call for “another Expert” almost a week ago! <*grin*>>
I know, just thanking you for coming on board...

I was just happy to see that the basic premise "create a saved query" is what you came up with as well...

Glad you posted something more "refined"


As you said, TransferSpreadsheet requires it. I also thought about using the report's Recordset, but it's only exposed in an ADP, so that's another dead-end.
Buddy, it always great having you around.

I just wish you wouldn't keep disappearing for months on end...
Many, many thanks to you both!  I was able to combine both of your suggestions into one that is working AMAZING for our needs!

I build my filter on the the first form and open the filtered report.  Then inside the filtered report's header I have an export button that builds a saved query using the report's recordsource and filter, then exports the query to excel.  

It's exporting just the query in a very standardized spreadsheet format (no messy report formatting anymore) - plus I'm able to allow the users to export several more fields which are in the query but not in the report due to space limitations.  So I have basically given my users access to a bunch more data columns using the export and they no longer have to deal with the way the data was grouping in excel.

IT IS PERFECT!!!  You both are GENIUS. is my code

Private Sub btnOpenRptAct_Click()

Dim strAdvisor As String
Dim strActivity As String
Dim strView As String
Dim strWhere As String
Dim lngLen As Long    'length of criteria string

'If an advisor is selected for search criteria then pass value into search
If Not IsNull(Me.cbxAdvisors) Then
    strAdvisor = Me.cbxAdvisors
    strWhere = strWhere & "([AdvisorFName]=   '" & strAdvisor & "') AND "
End If

'If an Activity Status is selected for search criteria then pass value into search
If Not IsNull(Me.cbxActivity) Then
    strActivity = Me.cbxActivity
    strWhere = strWhere & "([Activity Status]= '" & strActivity & "') AND "
End If

'If a View Status is selected for search criteria then pass value into search
If Not IsNull(Me.cbxViewStat) Then
    strView = Me.cbxViewStat
    strWhere = strWhere & "([ViewStatus]=   '" & strView & " ') AND "
End If

'See if the strig has a trailing "AND" to remove (length more than 5 characters)
lngLen = Len(strWhere) - 5
If lngLen <= 0 Then                                 'nothing int the string
DoCmd.OpenReport "rpt_Activity", acViewReport
'open report showing all records
Else                                                'criteria entered
    strWhere = Left$(strWhere, lngLen)              'remove trailing "AND"
DoCmd.OpenReport "rpt_Activity", acViewReport, , strWhere
End If

End Sub

Private Sub ExportExcel_Click()
Dim dbs         As DAO.Database
Dim qdf         As DAO.QueryDef
Dim strSQL      As String
Dim strQueryName As String

Set dbs = CurrentDb()
strQueryName = "qry_ActivityTemp"
strSQL = " SELECT *" _
        & " FROM (" & Reports(0).RecordSource & ")" _
        & " WHERE " & Reports(0).Filter & ";"
    If DCount("Name", "msysobjects", "Name=" & "'" & strQueryName & "'") > 0 Then
        DoCmd.DeleteObject acQuery, strQueryName
    End If
    Set qdf = dbs.CreateQueryDef(strQueryName, strSQL)

    DoCmd.OutputTo acOutputQuery, strQueryName, acFormatXLS, , True
    End Sub
Thank you for you enthusiastic feedback and kudos on your final code.

Let me add two “closing suggestions” for good measure:

1) Since ExportExcel_Click resides on the report, replace Reports(0) with Me, Me.Report, or Report. Some experienced users might have more than one report open at a time... If this was generic code, you could use Screen.ActiveReport for the same reason.

2) You delete and recreate qry_ActivityTemp. This works (as you know), but you can also just rewrite the SQL string:

dbs.QueryDefs(strQueryName).SQL = strSQL
This is particularly useful if you haven't turned off the “Name AutoCorrect Options”.

Success with your project!
Great suggestions - thanks!