Is there a way of showing the filter criteria in the cover page of a report?

[LeighW]

Hi all,

I'd like a cover page that shows which filter criteria have been used when creating the report. If this can't be done/ is very hard to implement then I'll leave it be but I thought it would be a nice touch.

So lets say I have in my table (tbl_1) with fields,

ID
Title
Description
Permit
Approval
Date

The report (rpt_1) is linked to tbl_1 and has all fields on it

And from a form (frm_Search) I apply a filter to the permit and approval fields which opens up the main form (frm_Main) filtered to the records with just permit = RSR and approval = true

So when I check the filter query on frm_Main it shows
Field = Permit Criteria = RSR
Field = Approval Criteria = True

I then open the report with:

DoCmd.OpenReport "rpt_1", acViewPreview, , Me.Filter

How then could I show the filter criteria on the report?

If you need me to show how I created the filter I can provide it but I'm not sure how that would help in this instance.

Thanks for any help,

Leigh

[Rabbit]

You can use VBA to populate a label with the filter property in the OnLoad event of the report.

[TheSmileyCoder]

As Rabbit says, the OnLoad event would be one way of doing it, it could look something like this:

Private Sub Report_Load()
   If Me.FilterOn Then
      me.lblFilter.Caption=Me.Filter
   End If
End Sub

[NeoPa]

Certainly up to Access 2003, Reports don't have the Load event :-( Nor are the .Filter or .OrderBy set up by the time the Open event fires. I recently had to handle this in one of my projects. The concept is clumsy, but works.

Private Sub Report_Activate()
    Static blnRepeat As Boolean
 
    If blnRepeat Then Exit Sub
    blnRepeat = True
 
    Me.lblTitle.Caption = Me.Filter
End Sub

Typically, this will only ever run once at the start.

[TheSmileyCoder]

Good point, I was not attentive to that fact. Newer versions of access has mixed the concept of Form and reports so they are alot more similar. I have yet to figure out exactly what was gained by this approach....

I think it might make more sense to use the reportHeader_Format event, but I haven't tested it.

Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
   If Me.FilterOn Then
      me.lblFilter.Caption=Me.Filter
   End If
End Sub
 

NOTE: This will only work if the lblFilter is a control in the reportHeader.

[NeoPa]

Smiley:
I think it might make more sense to use the reportHeader_Format event, but I haven't tested it.

Good thinking, mainly. Unfortunately, I don't believe this fires unless there is a Report Header section defined (at least). If you build reports consistently then this won't work for you (unless all your reports have Report Headers of course).

It does at least have the benefit of only ever firing once though, which is good in that respect.

[LeighW]

Thanks a lot guys,

I've annoyingly got Access 2003 so I haven't got the "On Load" event in the report.

I've tried both NeoPa's and Smiley's methods and for some reason Smiley's method refuses to fire while Neopa's does.

Now I have to work on changing the format to a more human view somehow so it doesn't show up like:

(([Permit = RSR) AND [Approval] = True)

But more like Permit = RSR, Approval = Yes

Could get a bit fiddly as there can be a number of different filters.

[Rabbit]

You can use the replace function to get rid of the parentheses and change the AND to a comma.

[NeoPa]

No surprise to see a clever suggestion from Rabbit. That may well be all you need.

Alternatively, if you need something more flowery, you can build the string in the form that calls the report and pass it as an OpenArgs parameter to the report. Generally, I would tend to go for a more simple solution like Rabbit's one if you can get away with that level of simplicity.

[TheSmileyCoder]

As NeoPa pointed out my suggestion will only fire if the report has a (visible) report header. I presumed (my bad) that you had one, as to me that would be the logical place to display the filter used.

I shouldn't make assumptions without explicitly stating so.