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

Heres one

Monday

No one will get up out of bed for school

Friday

House is total happiness - no more homeworks to help with

Martin (<:



Quoting John Skolits <JSkolits at CorporateDataDesign.com>:

> Here is some real old stuff that I got from somewhere. I'm sure someone
> will
> find a flaw in it someplace.
> 
> 
> Function StartOfWeek(ByVal D As Date) As Date
>     StartOfWeek = D - WeekDay(D) + 1
> End Function
> 
> Function EndOfWeek(ByVal D As Date) As Date
>      EndOfWeek = D - WeekDay(D) + 7
> End Function
> 
> Function EndOfMonth(ByVal D As Date) As Date
>      EndOfMonth = DateSerial(Year(D), Month(D) + 1, 0)
> End Function
> 
> Function StartOfMonth(ByVal D As Date) As Date
>     StartOfMonth = DateSerial(Year(D), Month(D), 1)
> End Function
> 
> 
> There ya' go. Hope they work.
> 
> John Skolits
> 
> -----Original Message-----
> From: accessd-admin at databaseadvisors.com
> [mailto:accessd-admin at databaseadvisors.com]On Behalf Of Gustav Brock
> Sent: Thursday, January 30, 2003 1:14 PM
> To: Seth Galitzer
> Subject: Re: [AccessD] HOW TO: Determine start and end of week given a
> specific date
> 
> 
> Hi Seth
> 
> That looks nice, Seth, but as you know it isn't of much value on this
> side of the ocean where the first day of the week is Monday according
> to the ISO standard.
> 
> I have two generic functions for this purpose which tries to follow
> the Access conventions for dealing with dates and day values and using
> the built in constants where possible. They should fit for any
> environment (I won't use that in..-word to spare JC who is busy for
> moment).
> 
> The default value for the weekstart is set to Monday for various
> reasons but could easily be changed to vbUseSystemDayOfWeek.
> 
> It doesn't turn out as simple as yours (and Charlotte's) but, anyway,
> here goes:
> 
> <code>
> 
> Function DateWeekFirst( _
>   ByVal datDate As Date, _
>   Optional ByVal lngFirstDayOfWeek As Long = vbMonday) _
>   As Date
> 
> ' Returns the first date of the week of datDate.
> ' lngFirstDayOfWeek defines the first weekday of the week.
> ' 2000-09-07. Cactus Data ApS.
> 
>   ' No special error handling.
>   On Error Resume Next
> 
>   ' Validate lngFirstDayOfWeek.
>   Select Case lngFirstDayOfWeek
>     Case _
>       vbMonday, _
>       vbTuesday, _
>       vbWednesday, _
>       vbThursday, _
>       vbFriday, _
>       vbSaturday, _
>       vbSunday, _
>       vbUseSystemDayOfWeek
>     Case Else
>       lngFirstDayOfWeek = vbMonday
>   End Select
> 
>   DateWeekFirst = DateAdd("d", vbSunday - _
>     WeekDay(datDate, lngFirstDayOfWeek), datDate)
> 
> End Function
> 
> Function DateWeekLast( _
>   ByVal datDate As Date, _
>   Optional ByVal lngFirstDayOfWeek As Long = vbMonday) _
>   As Date
> 
> ' Returns the last date of the week of datDate.
> ' lngFirstDayOfWeek defines the first weekday of the week.
> ' 2000-09-07. Cactus Data ApS.
> 
>   ' No special error handling.
>   On Error Resume Next
> 
>   ' Validate lngFirstDayOfWeek.
>   Select Case lngFirstDayOfWeek
>     Case _
>       vbMonday, _
>       vbTuesday, _
>       vbWednesday, _
>       vbThursday, _
>       vbFriday, _
>       vbSaturday, _
>       vbSunday, _
>       vbUseSystemDayOfWeek
>     Case Else
>       lngFirstDayOfWeek = vbMonday
>   End Select
> 
>   DateWeekLast = DateAdd("d", vbSaturday - _
>     WeekDay(datDate, lngFirstDayOfWeek), datDate)
> 
> End Function
> 
> </code>
> 
> /gustav
> 
> 
> > 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
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>