Access 2010 Report Filter problem


I have an access 2010 report filter problem

In VBA I created some code to filter my data.

When I open the report the data is not filtered. However, if I immediately go into design view then
back to report view the data IS filtered. So I know it works.

Also, if I check the report properties, I can the correct filter and FilterOnLoad = yes

Is this a timing issue?

I have tried moving my code from OnOpen to OnLoad to OnActivate. Same thing happens

When I initially open the report, no filtering, but if I go into design view then back to report view, the filter works


I welcome your comments

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
Can you provide the code you are using to filter the report?

Where are you getting the information to define the filter from?

I generally do this using the WHERE argument of the OpenReport method, but have done it other ways as well.
HenryV1955Author Commented:
Hello Dale Fye, thanks for replying

Sure, some more info. The filter I'm using is in a subreport. The query is an aggregate of data. The main report is unbound. Im putting some values in there from other code. Its like a scorecard

However, at the moment, I'm just running the subreport to get the filter working as this is where the code is anyway.

Since I was having trouble getting this to work , I simplied my filter parameters down to this just to get it working

Dim varTimeFilter As String, sql1 As String, sql2 As String, varDateFrom As String, varDateTo As String
    sql1 = "#7/10/2014#"
    sql2 = "[GLDate] = " & sql1

    varTimeFilter = sql2

    Me.Filter = varTimeFilter

    Me.FilterOnLoad = True

Again, I've tried this in OnOpen, OnLoad, OnActivate. It doesnt work when I open the report initially, but if I then open it in design view, then immediately go to report view, it works. I can also see my filter in the filter property of the report.

I have seen some info out there, that says this is a problem and some solutions are to open the report in design mode hidden, save the filter, then run it. I hope thats not the solution here.

Dale FyeOwner, Developing Solutions LLCCommented:
1.  Why aren't you simply using:

me.filter = "[GLDate] = #7/10/2014#"
me.filterOn = true

2.  Have you tried doing this from the main reports Load event?  If the main report is not bound, then it means that your subreport is not linked (master/child) to anything.  It also means that the main reports Load event may not fire either.  You might want to try setting the main reports TimerInterval to 100 (.1 sec) and then use the Timer Event to set the filter on the subreport.
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

HenryV1955Author Commented:
I even tried running this code from a blank form

DoCmd.OpenReport "subrptMinistrySummaryResultRandomCat1s", acViewDesign
    DoCmd.Close acReport, "subrptMinistrySummaryResultRandomCat1s", acSaveYes
    DoCmd.OpenReport "subrptMinistrySummaryResultRandomCat1s", acViewPreview

In design mode the code does run, it saves closes and opens. But the filter doesnt work. IT DOES appear in the property setting. Again if I open the report in design mode then immediately back to preview, the data is filtered

So whats happening is the property is being set, but the data doesnt filter til i open in design then back to preview manually. Then its fine. The issue isnt the filter itself. Its when its applied.
HenryV1955Author Commented:
Its interesting, I do alot of form filtering the same way. No problems at all.

But reports, is just a pain. Why dont they work the same way?
Dale FyeOwner, Developing Solutions LLCCommented:
Well, one way to skin this cat would be to create a Recordsource for the main form which is only the distinct values of GLDate between a range of dates, or for a specific date based the form you are calling the report from.  Then you could link the subreport to the main report on the two date fields and when you open the report, you would pass it your criteria

Docmd.OpenReport "mainReport", acviewPreview, , "[GLDate] = #7/10/2014#"

Or, the query for the main report could look something like:

SELECT DISTINCT GLDate FROM someTable WHERE [GLDate] = Forms!yourFormName.txt_GLDate

This would provide you with a single record in the main forms recordset, and you would not need to pass the WHERE argument via the OpenReport method.
I just finished a database that had the same problem. The report had two subreports that each displayed a chart. The database used a form to present report selection choices, and then a button created and opened the report. I got my report to work/refresh by using a QueryDef as the source for the charts on the subreports. The button on the selection form updated the QueryDefs with the user selections, and then opened the report. In other words, I didn't filter the report itself - I changed the WHERE clause in the QueryDef to match the user selections first, then opened the report.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HenryV1955Author Commented:
Allen Browne, noted Access MVP, shared this info on his website. Though its from a few years ago, I believe may still be relevant

3. FilterOn is not set reliably for reports

If you open a form with a WhereCondition, Access reports the form's FilterOn is True. If you open a report with a WhereCondition, Access may not set its FilterOn property. Any code in the report's events is therefore unable to determine whether if the Filter is applied or not.

To demonstrate this bug:

Open the Immediate window (Ctrl+G).
Enter this command:
    DoCmd.OpenReport "FilterOnProblem", acViewPreview, , "TheDate = #1/1/2003#"
In the Immediate window, ask Access if a filter is applied:
    ? Reports!FilterOnProblem.FilterOn
You will find that Access has set the report's Filter property to match the WhereCondition, but the FilterOn property has not been set.
Dale FyeOwner, Developing Solutions LLCCommented:

Allen has documented these bugs extremely well and I use his site frequently.
Although I cannot ever recall ever having that happen to me, it may have.  But that would be a good reason to create a recordsource for the main form that returns a single record.

I have a table (tbl_Numbers), which contains a single field (lngNumber) and 10 records (the values 0 through 9).  With this table, I can create a query through the used of Cartesian joins which can create a vast number of numeric values from 0 through some very large number.  In this case, I would create a query using that table that looks like:

SELECT DateAdd("d", lngNumber, #7/10/2014#) as RptDate
FROM tbl_Numbers
WHERE lngNumber = 0

You could replace the hard coded date with a reference to a control on your form that calls the report.

Then set that query as the RecordSource for your report and link the main report and the subreport with RptDate as the Master and [GLDate] as the child.

Or, like Thomas mentioned above, just change the WHERE clause of the subreports recordsource by modifying the SQL of that report.  If you do this, I would do it in the Click event of the button that calls the report.

Private Sub cmd_ReportPreview_Click

    Dim intPosn as integer
    Dim strSQL as string

    with currentdb.querydefs("subReportQueryName")
        intPosn = instr(.SQL, "WHERE")
        if intPosn > 0 then strSQL = LEFT(strsql, intPosn)
        strSQL = strSQL & " WHERE [GLDate] = #7/10/2014#"
        .sql = strsql
    end with

    Docmd.OpenReport "yourReportName", acViewPreview

End Sub

Open in new window

HenryV1955Author Commented:
Hi all, I have a solution. I do recall reading something about this from somewhere

I modified one of my previous comments

i appreciate all your comments. Just wanted to state again, my main report isnt bound because I'm getting values from different places and placing it on my scorecard. So I cant use master and child links etc. The subreport is bound to a query, however, it does have data recs I'm showing. So I need a way to filter just it. This report is driven from a form

Heres what works
Ok so after testing numerous times, this works.

From the Form on click to drive the Main Report, set the properties for the subreport first

'this will be the report filter
strFilter = "whatever you want here"

'Open the report in design view, you can do it hidden once in production, otherwise you can see it set the property as you walk thru this code
DoCmd.OpenReport "SubReportNameHere", acViewDesign, , , acHidden
'Set the filter property
Reports!SubReportNameHere.Filter = strFilter
'also set the filter on load property or it doesnt work
Reports!SubReportNameHere.FilterOnLoad = True

'Save the report    
DoCmd.Close acReport, "SubReportName", acSaveYes
'open the Main report
DoCmd.OpenReport "MainReportName", acViewPreview

The sub report data is filtered

So Reports do indeed Not behave like forms when it comes to setting filter properties on the fly. You need to set and save them first

Dale FyeOwner, Developing Solutions LLCCommented:
Henry,  since we don't actually have a sample database to work with, I do not seem to be able to convey for you the process I would use to do this.  But if the technique you are using is working, great.

However, I have numerous applications where I open the report (without filters) and use code in the Click event on the main FORM to loop through a recordset, set the filter on the report, and then either save or email the report (filtered).  I only open the report once and then filter it as many times as necessary based on code on the Form which opened the report.

I also disagree that you cannot bind your main report, even though you are filling in a lot of fields via code.  That report can be bound to a query that returns a single record and still allows you to fill in all of the unbound controls on that report via code.

There is more than one way to skin a cat, even when documented bugs are involved.
HenryV1955Author Commented:
Hi Dale

I just realized that when I convert my db to accde, you cannot open a report in design mode......stupid me

i appreciate your comment. I do use recordsets and sql strings with where clauses all the time as i do here for the main report. Everythings fine, just not being able to set the subreport filter at run time

It is interesting you can set a form filter on the fly but not a report. Hmm So working on another way to skin the cat as you say lol

HenryV1955Author Commented:
Hi all

Well I gave up setting report filtering. Unlike forms where its fast easy to set and change and works well on the fly, filtering reports with the filteron property dont work well at all

 So....I've gone back to setting the subreport recordsource on the fly with my variable filter in the Where clause.

Works fine

Dale you touched on this in one of your posts so going to award you the points.

HenryV1955Author Commented:
Sorry correction, split points between Thomas and Dale

My mistake

Dale FyeOwner, Developing Solutions LLCCommented:
That's fine.

I still don't know why you don't simply create a query that returns a single date as the Recordsource for your main report and then link this to your subReport.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.