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

Add Filter to Report

P: n/a
Hi,

I have a report that works just fine.
Now I would like to add the capability to choose selection criteria to
limit what is displayed. I created several reports that do this, but
they used VBA to create SQL statements, then apply them. Now that I
have been using Access/VBA for awhile, I am wondering if there is a
simpler way to do this than coding these long sql statements.

My report has a query as its record source. Can I just add the filter
criteria to this query somehow? I have read threads about
"WhereCondition", and I have also read about using a me.filter on the
docmd.openreport statement. Any advice on which is the best way to go?
Please include details for coding, as I am still a newbie.

Thank you in advance,
Lori

ps - is there a way to set my profile up so that I get an email when I
get a response to my thread? I thought I used to have this.

Feb 23 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If the criteria is something simple like a beginning date and an ending
date you can set up the query like this:

PARAMETERS Forms!frmReportCriteria!txtBeginDate Date,
Forms!frmReportCriteria!txtEndDate Date;
SELECT *
FROM table_name
WHERE date_column BETWEEN Forms!frmReportCriteria!txtBeginDate And
Forms!frmReportCriteria!txtEndDate

In the report's Open event put this:

Private Sub Report_Open(Cancel As Integer)

Const FRM_CRIT = "frmReportCriteria"

' Using acDialog suspends this code until the
' criteria form closes or is hidden.
DoCmd.OpenForm FRM_CRIT, WindowMode:=acDialog

If Not CurrentProject.AllForms(FRM_CRIT).IsLoaded Then
' The criteria form was closed by the user
Cancel = True
' Else
' The criteria form has the needed criteria & the
' user has clicked the OK button.
' So, continue opening the Report.
End If

End Sub

In the frmReportCriteria, have the 2 Text Boxes (txtBeginDate and
txtEndDate) and 2 Command Buttons (cmdOK and cmdCancel). The Command
Buttons control whether the report runs or not. cmdCancel will close
the criteria form and the If ... IsLoaded Then statement in the report
will stop the report. When the cmdOK button is clicked, the report
IsLoaded evaulation will return True and the report will continue.

In the criteria form here are the Command Button events:

Private Sub cmdOK_Click()
' Check to see if both Text Boxes have data --
' I'll leave that to you...

' Then Hide the criteria form. This will allow the
' suspended VBA code in the report to continue at the
' If ... IsLoaded Then statement.

Me.Visible = False

End Sub

Private Sub cmdCancel_Click()
DoCmd.Close
End Sub

If there are more criteria than can be easily handled in the query's
PARAMETER statement, you can use the report's Filter property like this:

Set up the Report's Open event:

Put this in the Report's Declaration section of its VBA module:

Const FRM_CRIT = "frmReportCriteria"

Private Sub Report_Open(Cancel As Integer)

' Using acDialog suspends this code until the
' criteria form closes or is hidden.
DoCmd.OpenForm FRM_CRIT, WindowMode:=acDialog

If Not CurrentProject.AllForms(FRM_CRIT).IsLoaded Then
' The criteria form was closed by the user
Cancel = True
Else
' The criteria form has the needed criteria & the
' user has clicked the OK button.

' Run the Filter set up routine
SetUp

End If

End Sub

Private Sub SetUp()

' Set up the Report's Filter
Dim strFilter As String
Dim frm As Form_frmReportCriteria

Set frm = Forms(FRM_CRIT)

' There must be a column named SalesDate in the query
strFilter = "SalesDate BETWEEN #" & frm!txtBeginDate & "#" & _
" AND #" & frm!txtEndDate & "#"

' Set up the Report's filter
Me.Filter = strFilter
Me.FilterOn = True

End Sub

If there are more criteria on the criteria form you can concatenate them
into the strFilter using SQL WHERE clause syntax without the word WHERE.
The advantages of the Report Filter method is you can choose which
criteria on the criteria form to include in the report's Filter, which
makes the report very flexible. The disadvantage to this method is the
query may return a large recordset that the report may not use, which
will cause the query/report to run slower.

The advantages of the parameters in the query is the query/report will
run faster 'cuz the query uses the WHERE clause to eliminate all rows
that are not needed before sending the data to the report.

It's up to you to determine which is the best suited to your situation.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQ/4lkoechKqOuFEgEQJG6ACdEOfAYznnsfNqJeYIOMfOg02HhQkA nAgb
ev6w6tIiqsEK1G+cZbTef6YP
=5RqF
-----END PGP SIGNATURE-----
lorirobn wrote:
Hi,

I have a report that works just fine.
Now I would like to add the capability to choose selection criteria to
limit what is displayed. I created several reports that do this, but
they used VBA to create SQL statements, then apply them. Now that I
have been using Access/VBA for awhile, I am wondering if there is a
simpler way to do this than coding these long sql statements.

My report has a query as its record source. Can I just add the filter
criteria to this query somehow? I have read threads about
"WhereCondition", and I have also read about using a me.filter on the
docmd.openreport statement. Any advice on which is the best way to go?
Please include details for coding, as I am still a newbie.

Feb 23 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.