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

 

[rgbanse]

If I understand:
create a new table with two fields
start_dt
end_dt
enter appropriate dates in each field

in the criteria for the query enter

between dlookup("[start_dt]","TableName&quot and dlookup("[end_dt]","TableName&quot

give it a try
RGB

 

[SebAguilera]

Thanks RGB,
I can query a range of dates properly. This is not a problem. The problem is rather that I do not want to have to manually update the start date and end date of 50 different queries every week. Im pretty sure I need a VB macro of some type to do this. Also I am asking because it would be interesting to know how to modify queries using VB not just those pertaining to dates.

Thanks
Seb

 

[rgbanse]

check into
docmd.runsql(" ;&quot
you can declare variables for use within the SQl String
RGB

 

[rgbanse]

Function SQLExample()
Dim PlusDays As Double
PlusDays = 10
'creates a table of with variable days to add to Date()
DoCmd.RunSQL ("SELECT Date() AS start_dt, " & _
"Date()+" & PlusDays & " AS end_dt INTO StartStop; &quot
End Function

RGB