Inserting date parameters through macros

[jhays]

I have a couple of Access reports that are weekly sales reports and they run just fine, but, call me lazy, I would like to know if there is a way to have a macro automatically feed a date range to the date parameters in the report. Basically, I want the macro to insert that week's Monday date into the beginning date parameter and the Friday date into the ending date parameter. Thanks very much for any help

 

[padinka]

I don't know of a way to do that but you can plug this into your query instead of parameters and it will provide the date range you are looking for...

I'm assuming you want the previous week's data and not the current week information here.

So plug this into the criteria of your query.

Between Format(DateAdd("d", (-Weekday(Now) - 6), Now()), "mm/dd/yy&quot and Format(DateAdd("d", (-Weekday(Now)-1), Now()), "mm/dd/yy&quot

If you ran this this week you would get data

Between 5/20/02 and 5/24/02

Next week you would get data

Between 5/27/02 and 5/31/02

One note of caution here. If your field is formatted for DATES AND TIMES, you must add a day to the END date when using the between function because it assumes that the time would be 00:00 so the function would be ...

Between Format(DateAdd("d", (-Weekday(Now) - 6), Now()), "mm/dd/yy&quot and Format(DateAdd("d", (-Weekday(Now)), Now()), "mm/dd/yy&quot


Trisha
padinka@yahoo.com