UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome Guest ( Log In | Register )

 
 Reply to this topicStart new topic
> Reporting Current Week    
 
   
MsfStl
post Jun 24 2008, 09:22 AM
Post #1

UtterAccess Addict
Posts: 225
From: St. Louis
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
Go to the top of the page
 
+
Alan_G
post Jun 24 2008, 09:37 AM
Post #2

Utterly Yorkshire and Forum/Wiki Editor
Posts: 15,884
From: Devon UK
Hi

Have a look here where you should find your answer.........
Go to the top of the page
 
+
MsfStl
post Jun 24 2008, 09:43 AM
Post #3

UtterAccess Addict
Posts: 225
From: St. Louis
Excellent! I've got that book marked now, thanks for the help, Alan!
o!

Scot
Go to the top of the page
 
+
Alan_G
post Jun 24 2008, 09:45 AM
Post #4

Utterly Yorkshire and Forum/Wiki Editor
Posts: 15,884
From: Devon UK
No problem - glad you found what you were looking for (IMG:http://www.utteraccess.com/forum/style_emoticons/default/sad.gif)
Go to the top of the page
 
+
MsfStl
post Jun 24 2008, 03:23 PM
Post #5

UtterAccess Addict
Posts: 225
From: St. Louis
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....
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


I hope this is of use to those looking for a quick way to create a "canned" report for the current week.

Peace!

Scot
Go to the top of the page
 
+
« Next Oldest · Access Reports · Next Newest »
 

Thank you for your support! Reply to this topicStart new topic

Jump To Forum:
 

Display Mode: Standard · Switch to: Linear+ · Switch to: Outline

Track this topic · Email this topic · Print this topic · Subscribe to this forum

RSS Go to Top  ·  Lo-Fi Version Time is now: 23rd May 2013 - 06:56 AM