-----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.
