Date Variable Union Query

[ewd97]

I have a union query where I am combining several queries into one. They all are searching by the same date. I have the statement "WHERE benefit_Query.Date=[Enter Date]" and every other query in the union query is set to be equal to "Benefit_Query.Date", but when the query is run, it prompts for the date for every individual query.

I checked and the individual queries are not set to prompt. What I need to know, is there a way to set a variable at the begining that can be used throughout the union query for the date. In otherwords, when the union query is run, it will prompt for the date, and all other queries in the union will be equal to that date.

Thank you.

 

[TonyJollans]

Hi ewd97,

If you make all the queries "prompt" for the same name, you should only be prompted once; once Access has a value for the variable it doesn't need to ask again, so ..

[tt][blue]SELECT Field1, Field2
FROM benefit_Query
WHERE benefit_Query.[Date]=[Enter Date]
UNION ALL
SELECT Field1, Field2
FROM benefit_Query_2
WHERE benefit_Query_2.[Date]=[Enter Date][/blue][/tt]

.. should only prompt once and use the same date in each query.

Enjoy,
Tony

------------------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading FAQ222-2244 before you ask a question.