What you should do is leave the query as it is and specify a
WhereCondition for the OpenReport action
***
DoCmd.OpenReport reportname
[, view]
[, filtername]
[, wherecondition]
ie:
DoCmd.OpenReport "MyReport", _
acViewPreview,, _
"city IN ('Denver','Chicago')"
***
Report Filters
It would be best to build a filter string for the report (as
opposed to imbedding a parameter in a query)--in this way,
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 Where
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.controlname_for_text & "'"
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.controlname_for_date & "#"
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.controlname_for_number
end if
if len(mfilter) > 0 then
DoCmd.OpenReport "ReportName", _
acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif
'~~~~~~~~~~~~~~~~~~~~~~~
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
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) -- there is a
more efficient way to do this, but this method is easier to
understand
Then we are going to add the criteria for that filter
make sure that the referenced fields are in the underlying
recordset for the report.
For a Date Range, you would do:
'~~~~~~~~~~~~~~~~~~~~~~~
If not IsNull(me.date1_controlname) Then
mFilter = mFilter & "[DateFieldname]>= #" _
& me.controlname_for_date1 & "#"
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.controlname_for_date2 & "#"
end if
'~~~~~~~~~~~~~~~~~~~~~~~
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
