Change date range in all query objects

[UHNSTrust]

Hi,

I have inherited a database which has numerous (70+) queries all with a date range criteria (i.e. Between #01/01/2011# and #31/12/2012#). Each month someone has to change the date range in every query and then run it to get stats out.

Is there any quick vba code to loop through each object and search for a string in the SQL and replace it with another one?

Any help appreciated.

Thanks
Jonathan

 

[SkipVought]

Relate each date to the date that the report is expected to run.

For instance, if the report is always run during the first of the month, the craft each date with respect to a Date() within that range.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

And I should have added, using Date Functions.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[dhookom]

You could try create a one record table with two fields [DateFrom] and [DateTo]. Add this table to every query and replace the hard-coded dates with the fields.

Duane
Hook'D on Access
MS Access MVP

 

[sxschech]

The above suggestions are useful going forward, but to get there, you could use a tool like find and replace to change the hard coded values to then refer to the one record table as dhookum showed. Alternatively, you could create a form with two text boxes, one for the start date and one for the end date. Then the user could enter the dates on the form and the query would refer to these text boxes to grab the dates.

Here are find and replace links. The first is one that I use and cost around $40.

http://www.rickworld.com/products.html

This one is free, but haven't tried it.
vtools

http://www.skrol29.com/us/vtools.php