Access: How to pass parameters to report when WHERE clause is embedded in complex SQL

Question

I know the question of how to pass parameters has been asked a while back with the this Previous Stack Overflow question

However my situation involves SQL that is a bit more complex and cannot make use of the "where clause" filter available when opening a report. My SQL statement is an aggregate that produces a single row with one field for each aggregate. I have two parameters "@StartDate" and "@EndDate" that I need to pass into the query.

SELECT q1.CasesAssigned, q2.WarningsIssued  
FROM 
(SELECT COUNT(*) AS CasesAssigned 
FROM vwCaseDetail 
WHERE DateAssigned Between @StartDate AND @EndDate) as q1,  
(SELECT COUNT(*) AS WarningsIssued 
FROM vwWarningDetail 
WHERE DateIssued Between @StartDate AND @EndDate) as q2

What I would like to do is "popup" a dialog or small form to have the user enter the Start and End dates but I have no idea on how to pass the values into the query the report is based on. Any suggestions? Thank you.

Answer 1

All you need to do is call the report from a form. On this form you have two text boxes for your start and end dates.

The query then has to be changed to

WHERE DateAssigned Between [Forms]![frmReporting]![txtDate_from] AND [Forms]![frmReporting]![txtDate_to]