Subquery date prompt

[rwmyers]

I have a query with a subquery and want to put a date prompt in the subquery rather than hard coding the date as it can change. How do I do that? Below is the SQL from my query.

SELECT A.EmployeeID, dbo_Employee.FullName, A.PayPeriodEndDate, A.DentalInsurance, dbo_JobAssignment.DepartmentKey
FROM ((dbo_Benefits AS A INNER JOIN dbo_Employee ON A.EmployeeKey = dbo_Employee.EmployeeKey) INNER JOIN dbo_Status ON A.EmployeeKey = dbo_Status.EmployeeKey) INNER JOIN dbo_JobAssignment ON A.EmployeeKey = dbo_JobAssignment.EmployeeKey
WHERE (((A.PayPeriodEndDate)=[Enter the new pay period end date]) AND ((A.DentalInsurance)='R')
AND ((Exists
(SELECT B.EmployeeID
FROM dbo_Benefits B
WHERE (B.DentalInsurance = 'N' and B.PayPeriodEndDate = #11/19/2007#)
AND A.EmployeeID = B.EmployeeID))<>False);

THANKS!

 

[genomon]

A great place to start is "Create a parameter query that prompts for criteria each time it's run" in Access help.
It's easy to change to SQL view & copy after you have created the query in design view.

Knowledge is knowing a tomato is a fruit; Wisdom is not putting it in a fruit salad.

 

[dhookom]

faq701-6763

Duane MS Access MVP