Avatar of Member_2_1316035
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.
Microsoft Access

Avatar of undefined
Last Comment
Member_2_1316035

8/22/2022 - Mon
Jeffrey Coachman

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

See this sample
Database164.mdb
Member_2_1316035

ASKER
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.
Jeffrey Coachman

<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, ...as you will have to allow for multiple fields, date ranges, Text and Number criteria, Nesting levels, ....and possibly And/Or Logic...
:-O


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.

;-)

JeffCoachman
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Member_2_1316035

ASKER
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.
ASKER CERTIFIED SOLUTION
Jeffrey Coachman

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Jeffrey Coachman

But another Expert may be along with a more efficient approach...
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

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...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Member_2_1316035

ASKER
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.
Jeffrey Coachman

For simplicity, I personally would go with the query approach...

So take your time and try all of our suggestions.

Then let us know...

;-)

Jeff
Member_2_1316035

ASKER
Simple is good : )  When you say "query approach", do you mean your first solution of building the temp query?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Jeffrey Coachman

My very first post where you dont even use  strWHERE
http://filedb.experts-exchange.com/incoming/2012/07_w27/587615/Database164.mdb

You build a query that looks at the form for the criteria...
Member_2_1316035

ASKER
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.
Jeffrey Coachman

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

JeffCoachman
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Member_2_1316035

ASKER
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?
Jeffrey Coachman

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

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

(°v°)
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Jeffrey Coachman

sorry h,

I lost track of who said what first
(or even what I said)....
\_O.o_/
harfang

No problem (cute ASCII art!) — (^v°)
Member_2_1316035

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
harfang

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, _
        WhereCondition:=strSQL
    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!
(°v°)
Member_2_1316035

ASKER
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.
Jeffrey Coachman

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

;-)

JeffCoachman
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Member_2_1316035

ASKER
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.
Jeffrey Coachman

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"

;-)

JeffCoachman
harfang

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

biang,

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

(°v°)
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Jeffrey Coachman

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

;-)


Jeff
harfang

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.
(°v°)
Jeffrey Coachman

Buddy, it always great having you around.

I just wish you wouldn't keep disappearing for months on end...
;-)
Your help has saved me hundreds of hours of internet surfing.
fblack61
Member_2_1316035

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

FYI...here 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)

    Application.RefreshDatabaseWindow
   
    DoCmd.OutputTo acOutputQuery, strQueryName, acFormatXLS, , True
   
    End Sub
harfang

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!
(°v°)
Member_2_1316035

ASKER
Great suggestions - thanks!
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.