Access 2010 Report Filter problem

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.

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.

Thanks
H

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.

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.

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?

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.

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.

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

I modified one of my previous comments

First
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

H

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.

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

Thanks
H

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.

Thanks
H

Sorry correction, split points between Thomas and Dale

My mistake

H

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.