You will only run this module ONE time. After you run this module all the queries in the database will have the hardcoded date range replaced with form variables as parameters.
Have you ever used the parameter prompt in a query? In Access you can have a query like this:
Code:
SELECT * FROM SomeTable WHERE MyDateField < [Please enter the date]
When you run this query Access will create a parameter prompt "form" and allow the user to enter the date. Then it takes the value the user entered and places that in the [Please enter the date] portion of the query. So if I enter 6/28/2007 in the prompt, the query will read and return records that match:
Code:
SELECT * FROM SomeTable WHERE MyDateField < 6/28/2007
The problem with this approach comes when you need multiple criteria. If you do this:
Code:
SELECT * FROM SomeTable WHERE MyDateField Between [Please enter the begin date] AND [Please enter the end date]
then you will have TWO prompts, once for the begin date and one for the end date.
Alternatively, instead of using the built in Access parameter prompts you can create your OWN form. To get a begin and end date at the same time, you would create a form (frmCriteria) that has two textboxes, name one txtBeginDate and txtEndDate, and a button that runs a query. Then your SQL would be:
Code:
SELECT * FROM SomeTable WHERE MyDateField BETWEEN Forms!frmCriteria!txtBeginDate AND Forms!frmCriteria!txtEndDate
you have to make sure that the form is open and there are values in the textboxes, when you press the button, the query runs using the values in the textboxes to generate the criteria of the WHERE clause.