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

[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.

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

 

[SgtJarrow]

Try this instead.....You mention that you want everything for the last seven day.....so place the following in your criteria:

Between Date()-7 And Date()

This will produce everything between today's date and the date seven days ago...if you want everything from yesterday back seven day's from then you would use:

Between Date()-8 And Date()-1

Using this method, you will NEVER have to go and change the values because the Date() is a system variable in Access that supplies today's date.... Only two things are infinite, the universe and human stupidity, and I'm not sure about the former. (Albert Einstein)

Robert L. Johnson III, MCSA, CNA, MCP, Network+, A+
w: robert.l.johnson.iii@citigroup.com
h: mstrmage@tampabay.rr.com

 

[SebAguilera]

Thanks Robert,
I've tried this, however it looks like my database doesn't like this variable. The query just generates 0 results when I try this and I know this to be false. I tried specifiying just "Date()" within the date field to get todays records but still got 0 results. Any other suggestions would be greatly appreciated.

Thanks Guys!

 

[SebAguilera]

Actually as a followup, I found out that the creators of this database set the Date field as Text rather than a Date type. I cannot change this since the data is automatically entered from a form on a website and would be dificult to change around. So I am still looking for a macro that will allow me to change the criteria of a field within mulitiple queries qithout haveing to manually open the query and set it myself.
Thanks