Joined: 30 Oct 2008
Location: United States
Online Status: Offline
Posts: 44
Topic: Dynamic Date Range Posted: 30 Nov 2011 at 6:37am
If I wanted to schedule a report every Monday and pull records based on a date range from 5/1/2011 to the previous Friday the report is being run on, how would I do that? For instance, if I were to schedule a report starting this upcoming Monday (Dec 5th), I would want the report to pull 5/1/2011 - 12/2/2011. Every week that last date would increase by 7 days. Thank you!
Joined: 30 Oct 2008
Location: United States
Online Status: Offline
Posts: 44
Posted: 30 Nov 2011 at 8:34am
Thank you for your quick response. I still foresee a problem. The 12\5\2011 date isn't constant. The report will be run every Monday. I need the the first date to remain constant which is 5\1\2011 which your formula provides for, but the end date will change. On 12\5, the report will pull 5\1\2011 through 12\2\2011 which is the preceding Friday. The following Monday (12\12\2011), the report will pull 5\1\2011 through 12\9\2011 which is 3 days prior to that report being run. The end date will always be 3 days prior to the day each weeks report is ran. Thanks again for your help.
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 01 Dec 2011 at 5:14am
in case you need a little more flexibility (like you can't always run the report on Mnnday or you occasionally run it ad hoc during the week) I think this would always give you the friday before the current day.
{table.datefield} in date(2011,5,1) to dateadd('d',-(weekday(currentdate,crsaturday)),currentdate)
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot delete your posts in this forum You cannot edit your posts in this forum You cannot create polls in this forum You cannot vote in polls in this forum
IP Logged
