Build the filter in the form that opens the report.
use combos or lists, then see what the user picked...and these will be the filter for the rpt qry.
You cant use form boxes in a query if there's nothing in them..so..
Test all controls for a possible filter then build the sql.
on btnRpt_Click()...
Code:
dim sSql as string, sWhere as string
if not isnull(cboState) then sWhere = sWhere & " and [state]='" & cboState & "'"
if not IsNull(txtName) then sWhere = sWhere & " and [Name]='" & txtName & "'"
if not IsNull(chkContact) then sWhere = sWhere & " and [Contact]=" & chkContact.value
'remove 1st And
sWhere= mid(sWhere,4)
sSql = "SELECT * FROM tblCompany WHERE " & sWhere
OpenRptFilter(pvRpt, pvQry)
end sub
PUBLIC SUB OpenRptFilter(pvRpt, pvQry)
dim rpt
DoCmd.OpenReport pvRpt, acViewDesign
Set rpt = Reports(pvRpt)
rpt.recordsource = pvQry
'rpt.OrderByOn = True
'rpt.OrderBy = sFld
DoCmd.Close acReport, rpt.Name, acSaveYes
DoCmd.OpenReport vRpt, acViewPreview
end sub