date query question (populating report)

[benzan]

im trying to populate report based on users selection of name and date range.

user selects name, from date, and to date and then report populates.
one problem, user must select date ranges to view the report.

is there a way to set it so that if they leave "fromdate" and "toDate" blank and will populate everything under the name they select?

here's the query criteria:
Between [forms]![rpt_pr]![txtfromdate] And [forms]![rpt_pr]![txttodate]

any help?

and the sql:
SELECT tbl_purchaseRequisition.PrNum, tbl_purchaseRequisition.StaffID, tbl_purchaseRequisition.AuthforPay, tbl_purchaseRequisition.WPM, tbl_purchaseRequisition.Amount, tbl_purchaseRequisition.PaID, tbl_purchaseRequisition.Bmon, tbl_purchaseRequisition.PurposeOfPurchase, tbl_purchaseRequisition.Actual, tbl_Payee.PaName, tbl_Payee.Address1, tbl_purchaseRequisition.Date
FROM tbl_Payee INNER JOIN tbl_purchaseRequisition ON tbl_Payee.PaID = tbl_purchaseRequisition.PaID
WHERE (((tbl_purchaseRequisition.StaffID)=[forms]![rpt_PR]![cbostaff]) AND ((tbl_purchaseRequisition.Actual)=No) AND ((tbl_purchaseRequisition.Date) Between [forms]![rpt_pr]![txtfromdate] And [forms]![rpt_pr]![txttodate]));

 

[benzan]

oops. typo. i wanna make the date range *optional*

 

[PHV]

Replace this:
Between [forms]![rpt_pr]![txtfromdate] And [forms]![rpt_pr]![txttodate]
with this:
Between [forms]![rpt_pr]![txtfromdate] And [forms]![rpt_pr]![txttodate] OR ([forms]![rpt_pr]![txtfromdate] Is Null AND [forms]![rpt_pr]![txttodate] Is Null)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SeeThru]

Or, build your filter string first, and open the report using the Where argument. It can be a lot more powerful.

if isnull(startdate) and isnull(endate) then
strSQL="(((tbl_purchaseRequisition.StaffID)=[forms]![rpt_PR]![cbostaff]) AND ((tbl_purchaseRequisition.Actual)=No) "
else
strsql=(((tbl_purchaseRequisition.StaffID)=[forms]![rpt_PR]![cbostaff]) AND ((tbl_purchaseRequisition.Actual)=No) AND ((tbl_purchaseRequisition.Date) Between [forms]![rpt_pr]![txtfromdate] And [forms]![rpt_pr]![txttodate]));
end if

docmd.openreport "myreport" ,acpreview,,strsql