building OpenReport Where parameter, SetReportFilter
---
Hi Aurora,
here is some generic information on using the WHERE clause of the
OpenReport action to limit records for a report
***
for the main report, you can use the WHERE clause of the OpenReport action
DoCmd.OpenReport reportname[, view][, filtername][, wherecondition]
ie:
DoCmd.OpenReport "MyReport", acViewPreview,, "city IN ('Ct','JHB')"
***
Report Filters
to build a filter string for the report, you can use comboboxes and
listboxes to make it easier for the user to choose criteria and you can
ignore criteria when it has not been specified...
here is an example that tests criteria and builds a filter string to use
as a parameter in OpenReport
assuming you are in the code behind the ReportMenu form...
'~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable to hold text
dim mFilter as string
'initialize the variable
mFilter = ""
'substitute YOUR controlname in here after "Me."
'we are testing to see if it is filled out
'if it is, we are going to make mFilter hold the criteria
If not IsNull(me.text_controlname ) Then
mfilter = "[TextFieldname]= '" & me.text_controlname & "'"
end if
'test the next control
If not IsNull(me.date_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname]= #" _
& me.date_controlname & "#"
end if
'test the next control
If not IsNull(me.numeric_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mfilter = mfilter & "[NumericFieldname]= " _
& me.numeric_controlname
end if
'----------------------- listbox
dim varItem as Variant _
, mListWhere as string
mListWhere = ""
For Each varItem In me.listbox_controlname.ItemsSelected
'delete the line that doesn't apply
'for text
mListWhere = mListWhere & "'" _
& me.listbox_controlname.ItemData(varItem) & "', "
'for numbers
mListWhere = mListWhere _
& me.listbox_controlname.ItemData(varItem) & ", "
Next varItem
if len(mListWhere) > 0 then
mListWhere = "[Field_Name] IN (" & mListWhere
'remove comma and space from the end and add a parenthesis
mListWhere = left(mListWhere,len(mListWhere)-2)) & ")"
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mfilter = mfilter & mListWhere
end if
'-----------------------
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
'~~~~~~~~~~~~~~~~~~
I have used
TextFieldname to show how text needs to be delimited - with single quote
marks (you can also use double quote marks
DateFieldname to show that dates need to be delimited with #
NumericFieldname to show that numbers are not delimited
for listbox criteria, a loop is done through the selected items and the
criteria is listed and seperated by commas
'~~~~~~~~~~~~~~~~~~
each time, we are testing to see if a filter control is filled out.
If it is, we are going to see if we first need to add AND (if the filter
string already says something)
Then we are going to add the criteria for that filter
the first condition tested, obviously, does not have anything in the
filter string yet <smile>
make sure that the referenced fields are in the underlying recordset for
the report.
Since a filter is applied on the recordset, they do not have to be on
the report object (unlike a form)
For a Date Range, you would do:
'~~~~~~~~~~~~~~~~~~
If not IsNull(me.date1_controlname ) Then
mFilter = mFilter & "[DateFieldname]>= #" _
& me.date1_controlname & "#"
end if
If not IsNull(me.date2_controlname ) Then
'if we alread have criteria, add AND to specify that and more
if mfilter <> "" then mFilter = mFilter & " AND "
mFilter = mFilter & "[DateFieldname] <= #" _
& me.date2_controlname & "#"
end if
'~~~~~~~~~~~~~~~~~~
If you are not using the American Date Format, then you will need to do
something like this:
Format(me.date_controlname, "\#m\/d\/yyyy\#")
instead of
"#" & me.date_controlname & "#"
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~`
Use this method for Filtering Subreports (before main report is opened)
or before Outputting Report or before using SendObject
'~~~~~~~~~~~~~~~
Sub SetReportFilter( _
ByVal pReportName As String, _
ByVal pFilter As String)
' written by Crystal
' Strive4peace2007 at yahoo dot com
' PARAMETERS:
' pReportName is the name of your report
' pFilter is a valid filter string
' USEAGE:
' SetReportFilter "MyReportname","someID=1000"
' SetReportFilter "MyAppointments", _
"City='Denver' AND dt_appt=#9/18/05#"
On Error Goto Proc_Err
'---------- declare variables
Dim rpt As Report
'---------- open design view of report
' --- and set the report object variable
'use the hidden parameter to open if you don't want to see it
DoCmd.OpenReport pReportName, acViewDesign
Set rpt = Reports(pReportName)
'---------- set report filter and turn it on
rpt.Filter = pFilter
rpt.FilterOn = IIf(Len(pFilter) > 0, True, False)
'---------- save and close the changed report
DoCmd.Close acReport, pReportName,acSaveYes
'---------- Release object variable
Set rpt = Nothing
Proc_Exit:
Exit Sub
Proc_Err:
msgbox err.description,, _
"ERROR " & err.number & " SetReportFilter"
'press F8 to step thru code and fix problem
'comment next line after debugged
Stop: Resume
'next line will be the one with the error
resume Proc_Exit:
End Sub
'~~~~~~~~~~~~~~~
Warm Regards,
Crystal
*
have an awesome day
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
I am using Access 2003.
I can open a form using a filter to access a drop down list for the user to
pick the item [material] that they want to work with. But I do not know how
to do this to a report. I want a user to be able to access a drop down list
to pick the material he wants a report on. I am not good with visual basic
programming, but I can copy/input if I have a sample to follow. Can anyone
point me in the right direction?
aurora
