What is the function to grab the current week (and dates) for a report?
I am creating a series of reports: Daily (today), Weekly (this week), and Date Range (User selected Start and End).
I have the Daily and the Date Range, but I am a bit perplexed on creating a "canned" weekly date.
Additionally, with the current week I would like to print the Monday Date and the Saturday/Sunday Date on the report (e.g., "Report for the week of Monday, June 23, 2008 to Sunday, June 30, 2008")
Thanks,
Scot
Full Version: Reporting Current Week
Excellent! I've got that book marked now, thanks for the help, Alan!
o!
Scot
o!
Scot
No problem - glad you found what you were looking for 
For those who may be looking at this thread in the future: What I've done is modified the query that the report is based on with a call to functions that returns the current beginning and end dates of the current week.
In the query SQL I have....
The functions are in a module I called modGetDates :
I hope this is of use to those looking for a quick way to create a "canned" report for the current week.
Peace!
Scot
In the query SQL I have....
CODE
WHERE OtherTests.TestDate Between (Get_WkStrt(Date()) And (Get_WkEnd(Date()))
...The functions are in a module I called modGetDates :
CODE
Option Compare Database
Option Explicit
Public Function Get_WkStrt(StartDate As Date) As Date
'********************************************************************************
****************
'* This function is used to pass the Beginning date of the current week to a query. *
'* Using Sunday as the beginning date. Modified: Jun 24, 2008 By:MSF *
'* Code referenced from "http://support.microsoft.com/kb/210604" *
'********************************************************************************
****************
Get_WkStrt = date - Weekday(date) + 1
End Function
Public Function Get_WkEnd(EndDate As Date) As Date
'********************************************************************************
****************
'* This function is used to pass the End date of the current week to a query. *
'* Using Sunday as the beginning date. Modified: Jun 24, 2008 By:MSF *
'* Code referenced from "http://support.microsoft.com/kb/210604" *
'********************************************************************************
****************
Get_WkEnd = date - Weekday(date) + 7
End Function
Option Explicit
Public Function Get_WkStrt(StartDate As Date) As Date
'********************************************************************************
****************
'* This function is used to pass the Beginning date of the current week to a query. *
'* Using Sunday as the beginning date. Modified: Jun 24, 2008 By:MSF *
'* Code referenced from "http://support.microsoft.com/kb/210604" *
'********************************************************************************
****************
Get_WkStrt = date - Weekday(date) + 1
End Function
Public Function Get_WkEnd(EndDate As Date) As Date
'********************************************************************************
****************
'* This function is used to pass the End date of the current week to a query. *
'* Using Sunday as the beginning date. Modified: Jun 24, 2008 By:MSF *
'* Code referenced from "http://support.microsoft.com/kb/210604" *
'********************************************************************************
****************
Get_WkEnd = date - Weekday(date) + 7
End Function
I hope this is of use to those looking for a quick way to create a "canned" report for the current week.
Peace!
Scot
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.