[AccessD] HOW TO: Determine start and end of week given a specific date

A simplified version is:

basGetWeekStart  = dte - WeekDay(dte) + 1

Charlotte Foust

-----Original Message-----
From: Seth Galitzer [mailto:sgsax at ksu.edu] 
Sent: Wednesday, January 29, 2003 12:55 PM
To: accessd at databaseadvisors.com
Subject: [AccessD] HOW TO: Determine start and end of week given a
specific date


Greetings all,

Welcome back to the new and (hopefully) improved list!

Just thought I'd share something I worked out just now, only 'cause I
thought it was spiffy and was sure newbies and oldies might find a use
for it.

So a user comes up to me and asks me to make a report that they would
need to generate from time to time which would contain a week's-worth of
data.  However, they might not always generate it on time and so would
need to be able to get data from any given week.  "No problem!" I told
them.  And it turns out I was right.

I'll leave the interface up to you, because the spiffy part is getting
the first and last day of the week.  We always wanted to get data from a
given week, starting with Sunday and ending with Saturday (standard
Western week period).  The solution lies in a little built-in function
called Weekday().  This function returns the ordinal value of the day of
the week given that day as a Date value.  So Weekday(#1/29/2003#) would
return 4. With a bit of simple math and the use of the DateAdd()
function, we can get either the start date or end date of a week given
any date with one line of code each.

Here are the two functions I wrote (error-hendler left out):

Public Function basGetWeekStart(dte As Date) As Date
    'Subtract the ordinal value of dte and add 1 to get the Sunday
    '   before dte
    basGetWeekStart = DateAdd("d", -(Weekday(dte)) + 1, dte) End
Function

Public Function basGetWeekEnd(dte As Date) As Date
    'Subtract the ordinal value of dte from 7 (vbSaturday = 7)
    '   and add that many days to dte to get the Saturday after dte
    basGetWeekEnd = DateAdd("d", vbSaturday - (Weekday(dte)), dte) End
Function

Note that by default, Weekday() uses Sunday as the start of the week,
but that can be overriden to suit your needs by adding one more
parameter value to the function call.  See Help on the function for
constant values to use.

This is the kind of code I like: simple, elegant, and relatively
self-explanitory.

Enjoy!

Seth

-- 
Seth Galitzer			sgsax at ksu.edu
Computing Specialist		http://puma.agron.ksu.edu/~sgsax
Dept. of Plant Pathology
Kansas State University

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd