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
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Inserting date parameters through macros
- Thread starter jhays
- Start date
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"
and Format(DateAdd("d", (-Weekday(Now)-1), Now()), "mm/dd/yy"
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" and Format(DateAdd("d", (-Weekday(Now)), Now()), "mm/dd/yy"
Trisha
padinka@yahoo.com
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"
and Format(DateAdd("d", (-Weekday(Now)-1), Now()), "mm/dd/yy"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"
and Format(DateAdd("d", (-Weekday(Now)), Now()), "mm/dd/yy"Trisha
padinka@yahoo.com
- Status
