By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,319 Members | 2,115 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,319 IT Pros & Developers. It's quick & easy.

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

LeighW
P: 73
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:
Expand|Select|Wrap|Line Numbers
  1. 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
Dec 13 '12 #1
Share this Question
Share on Google+
9 Replies


Rabbit
Expert Mod 10K+
P: 12,366
You can use VBA to populate a label with the filter property in the OnLoad event of the report.
Dec 13 '12 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
As Rabbit says, the OnLoad event would be one way of doing it, it could look something like this:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Load()
  2.    If Me.FilterOn Then
  3.       me.lblFilter.Caption=Me.Filter
  4.    End If
  5. End Sub
Dec 13 '12 #3

NeoPa
Expert Mod 15k+
P: 31,494
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Report_Activate()
  2.     Static blnRepeat As Boolean
  3.  
  4.     If blnRepeat Then Exit Sub
  5.     blnRepeat = True
  6.  
  7.     Me.lblTitle.Caption = Me.Filter
  8. End Sub
Typically, this will only ever run once at the start.
Dec 13 '12 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub ReportHeader_Format(Cancel As Integer, FormatCount As Integer)
  2.    If Me.FilterOn Then
  3.       me.lblFilter.Caption=Me.Filter
  4.    End If
  5. End Sub
  6.  
NOTE: This will only work if the lblFilter is a control in the reportHeader.
Dec 13 '12 #5

NeoPa
Expert Mod 15k+
P: 31,494
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.
Dec 13 '12 #6

LeighW
P: 73
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:
Expand|Select|Wrap|Line Numbers
  1. (([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.
Dec 14 '12 #7

Rabbit
Expert Mod 10K+
P: 12,366
You can use the replace function to get rid of the parentheses and change the AND to a comma.
Dec 14 '12 #8

NeoPa
Expert Mod 15k+
P: 31,494
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.
Dec 14 '12 #9

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.
Dec 14 '12 #10

Post your reply

Sign in to post your reply or Sign up for a free account.