Date parameters 1 time in union query 2

[cmmrfrds]

Each of the underlying tables in this union query has a Date field. How do I prompt for the date 1 time and apply to all the queries?
SELECT qry1Rpt.[Staff Assigned], qry1Rpt.Category, Sum(qry1Rpt.Hours) AS SumOfHours1
FROM qry1Rpt
Group By qry1Rpt.[Staff Assigned], qry1Rpt.Category
UNION
SELECT qry2Rpt.[Staff Name], qry2Rpt.Category, Sum(qry2Rpt.Hours) AS SumOfHours1
FROM qry2Rpt
Group By qry2Rpt.[Staff Name], qry2Rpt.Category
UNION SELECT qry3Rpt.[Staff Assigned], qry3Rpt.Category, Sum(qry3Rpt.SumOfHours) AS SumOfHours1
FROM qry3Rpt
Group By qry3Rpt.[Staff Assigned], qry3Rpt.Category
Having qry1Rpt.Date Between [type first range date ##/##/####] And [type second range date ##/##/###];

 

[rickj65]

You'll have to break up the SQL statement into 2 queries.

First save all of the UNION SELECT statements as one query without any parameters....lets say you name it "qryDateUnion"

Then create a second query that uses the "unionized" query as its record source and include the date range parameters there...

"SELECT * FROM qryDateUnion WHERE MyDateField Between [Enter Start Date] AND [Enter End Date]"

 

[scking]

Each query within the union query would have the exact same parameter prompt. Access will not propmpt more than once for the same parameter and will replace all instances of the prompt with the value you provide. So you would need to add a matching 'HAVING' clause to qry1Rpt and qry2Rpt.
-------------------------------------
scking@arinc.com
Try to resolve problems independently
Then seek help among peers or experts
But TEST recommended solutions
-------------------------------------