Hi Bret,
how about defining a report based on the query (with no criteria) then
using the Where clause parameter of OpenReport to limit records if
criteria is specified?
'~~~~~~~~~~~~~~~~~~~~~~~~~~
'tell Access you are going to create a variable
dim mFilter as variant
'initialize the variable
mFilter = null
'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
mFilter = (mFilter + " AND ") _
& "[DateFieldname]= #" & me.controlname_for_date & "#"
end if
'test the next control
If not IsNull(me.numeric_controlname ) Then
mfilter = (mFilter + " AND ") _
& "[NumericFieldname]= " & me.controlname_for_number
end if
if not IsNull(mfilter) then
DoCmd.OpenReport "ReportName", acViewPreview, , mfilter
else
DoCmd.OpenReport "ReportName", acViewPreview
endif
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
SPACE UNDERSCORE at the end of a line means that the statement is
continued on the next line
'~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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)
Then we are going to add the criteria for that filter
mFilter = (mFilter + " AND ") & ... ' some other criteria
make sure that the referenced fields are in the underlying RecordSource
for the report -- and it is sometimes necessary for the fields to be in
controls on the report (the Visible property can be No)
Warm Regards,
Crystal
remote programming and training
Access Basics
8-part free tutorial that covers essentials in Access
http://www.AccessMVP.com/strive4peace
*
have an awesome day
*
Hi, I have a 3 linked table query that works fine when I manually
enter selection criteria. I want to use a form to enter the criteria.
There are three fields I want to search on, when any of them are blank
on the form, the query doesnt work. This is because the empty fileds
on the form create I guess a Null on the query.
How can I make this work? Or is there a better way?
Thanks,
Bret
