MS Access / SQL - Applying a time condition to multiple queries at once

I have 300+ queries which fetch information from my database. Right no开发者_运维百科w, there is no time condition specified in the queries, so if I wanted to filter all of them by a certain time period, i.e. Between #07/01/2009# And #08/01/2009#, I would have to manually go in each query and add this condition.

All of my queries are populating data into 4 main reports. What I am trying to do is apply a time filtering criteria like the one above to all of my queries at once, so that I can create a weekly report, as well as the Totals report (which just means there's no time condition).

Is there any easy way to add a single parameter before pulling my report that would filter all of my queries at once, and to pull the Totals report if it the parameter field were blank?

You can use a reference to a form control within the WHERE clause of a query. So, for example, if I have an open form named frmDatePicker which includes a text box control named txtStartdate, I could use that control's value as a WHERE condition.

SELECT *
FROM MyTable
WHERE my_date_field >= Forms!frmDatePicker!txtStartDate;

That approach can work, but I have no idea whether it's an appropriate fit for your situation. You would have to modify all of your queries which include that date condition. That could be a one-time-only change. But if you ever change the form and/or control name, you'd have the revise the queries again. (So try not to do that!)

Edit: If you want to allow the user to leave txtStartDate blank, so as not to filter on that date at all, try a WHERE clause like this:

WHERE
    my_date_field >= Forms!frmDatePicker!txtStartDate
    Or Forms!frmDatePicker!txtStartDate Is Null;