Adding an automatic date range to a query in VBA access

Question

This is my first question, please be kind. I am writing a macro in access and I want a series of reports to run one after another. the problem is all report have a date range that needs to be entered. Some are for the previous week some are for the previous month.

Is there a way in VBA or the macro creator to automatically calculate a date range for the previous month or week and populate the field to fully automate the process without manually entering the date range each time.

I am a new to VBA. Any help would be great, just point me in the right direction. Have a good day.

Answer 1

This query is created using the query design window in MS Access and then cut from SQL view. It will show records for last week, where ww is week number, in table1

SELECT Table1.AKey, Table1.atext, Table1.ADate, 
       Format([ADate],"ww") AS Week, Month([ADate]) AS [Month], 
       Year([ADate]) AS [Year]
FROM Table1
WHERE (((Format([ADate],"ww"))=Format(Date(),"ww")-1)
AND ((Year([ADate]))=Year(Date())));

You will notice that one column is called Month. You can use this to set a previous month in a similar way to setting the previous week. For example, both last week and last month:

SELECT Table1.AKey, Table1.atext, Table1.ADate, 
       Format([ADate],"ww") AS Week, Month([ADate]) AS [Month], 
       Year([ADate]) AS [Year]
FROM Table1
WHERE (((Format([ADate],"ww"))=Format(Date(),"ww")-1) 
AND ((Year([ADate]))=Year(Date()))) 
OR (((Month([ADate]))=Month(Date())-1) 
AND ((Year([ADate]))=Year(Date())));

The SQL could be written much more neatly, but you may as well start with the query design window.

Answer 2

i guess the date has a own field in the database you open
then you can do something like this

strSQL = "SELECT * FROM reports WHERE Date >= " & now() -7
rs.open(strSQL)
' for the last week

strSQL = "Select * FROM reports WHERE Date >= " & now() - 30
rs.open(strSQL)
' for the last month

but you will need to format now() to the same format as it is in your Table and that is just kinda the rawest code. i had to handle something similar and this worked out quite well