User is prompted for same query parameter more than once

Question

I have a parameter query selecting information from my table based on the start date entered by the user and today's date. I want to search two fields in my table with the WHERE statement without entering the prompt for the starting date twice. Right now I have:

SELECT PatientSurvey.PatientID
FROM PatientSurvey
WHERE (PatientSurvey.[6MonthSurveyReturn] OR PatientSurvey.[12MonthSurveyReturn]) Between [Enter the last date checked for:] And Date();

This doesn't seem to work. But if I do the following:

SELECT PatientSurvey.PatientID
FROM PatientSurvey
WHERE (PatientSurvey.[6MonthSurveyReturn]) Between [Enter the last date checked for:] And Date() OR (PatientSurvey.[12MonthSurveyReturn]) Between [Enter the last date checked for:] And Date();

Then it prompts the user twice for the same input. How can this be prevented?

Answer 1

Add a PARAMETERS declaration to your query.

PARAMETERS [Enter the last date checked for:] DateTime;
SELECT PatientSurvey.PatientID
FROM PatientSurvey
WHERE
       (([PatientSurvey].[6MonthSurveyReturn]) Between [Enter the last date checked for:] And Date())
    OR (([PatientSurvey].[12MonthSurveyReturn]) Between [Enter the last date checked for:] And Date());