I am trying to run a query that will only show me employees who have retired within a specified date range. I have used and expression to prompt for a date entry, then used the datediff function to calculate the difference between the "EffectiveDate" and the Entered Date. When I attempt to put a stipulation to only show records where the DateDiff is less than or equal to 365 days, I get propted to enter the "EndDate" again. Below is the Code....I only want to enter the date once.
SELECT [Employee Status].EmployeeName, Employees.LastName, Employees.FirstName, Employees.MiddleName, Status.ID, Status.Status, [Employee Status].EffectiveDate, [Enter EOY Date as MM/DD/YYYY] AS EndDate, (Int(DateDiff("d",[EffectiveDate],[EndDate]))) AS CalculatedDateRange
FROM Employees INNER JOIN (Status INNER JOIN [Employee Status] ON Status.ID = [Employee Status].Status) ON Employees.[Employee ID] = [Employee Status].EmployeeName
WHERE (((Status.ID)=3) AND (((Int(DateDiff("d",[EffectiveDate],[EndDate]))))<=365));