In Access, how do I write a Macro that Modifies multiple queries?

[SebAguilera]

Every week I need to modify certain queries, run them and send them out to specific people. The queries find all records inserted within the last 7 days. Unfortunately,the genius designer specified the Date as a text field so I cannot use the Date() function[records are added automatically by a form on a web site that can't be changed]

This is what I enter under the Date field within each of the queries:

Between "05/20/*02" And "05/26/*02"

This finds all the records inserted between May 20,2002 and May 26,2002. Every week I must manually go in and change the dates on many queries and this can become tedious. Does anybody have a macro that can help me out?I want to write a macro that allows me to specify one date and will then change that search criteria within all the queries that I need. Even if I have to manually go into the Macro code and change the date this will be great rather than having to do it once for every query since I have like 50 queries to run.


Thanks in advance guys!!
Seb

 

[scriverb]

Two Options:

1. Create a new column in each query with the following pasted in the field row:
NewDateField: CDate([tablename]![DateFieldName])
Now you can use the Date() function to create your criteria expression for this new column:
Between DateAdd("d",-7,Date()) and DateAdd("d",-1,Date())

2. This takes quite a bit of code but I can provide it for you to parse out the same between statement and put it in the exact format that you need to make the string comparison.

I would recommend the first option. It is much cleaner and fairly easy to implement. If you choose the second I can provide that for you. Bob Scriver