PLEASE SKIP TO MY LAST POST UNLESS YOU WANT BACKGROUND INFO
I've created a report that displays a date range of
BETWEEN @StartDate AND @EndDate
with @StartDate and @EndDate as parameters.
The defaults are @StartDate=DateAdd("d",-7,
Today()) and @EndDate=Today() but we'd like to change the default date range to the last pay period. So if today is March 21, then the last pay period would be Monday March 12 to Sunday March 18 (at midnight). Employees would enter their hours on Monday March 19th and Tuesday March 20th (and every Tuesday) they would be sent the report through scheduled events we set up.
This would be the default set up. If they want to see hours for another period I want them to be able to choose a period rather than choosing a start date and end date as I have now. I was thinking a drop-down list of date ranges would be the easiest way though a calendar would be visually more engaging.
Is there a way to dynamically set this up? I know there are functions for date that lets me return things like the integer value of the week and I've seen code where people use a date and figure out the first day of that month so I was wondering if there is a way to take todays date and find the previous week's range? Would it be best to put something like this into a stored procedure? A condition where if today's date is Tuesday then the pay period is last week monday to this past sunday?
Or if I'm making things more complicated than they can be does anyone know a way to implement this functionality into a report?