Third Sunday in the Month
Mark R
current year. Any quick ideas?
Alberto Borges, MD
current year. Any quick ideas?>>
As Henry Ford said: You can have well built, fast and cheap- any 2 of the
above! The following is anything but quick... but it's cheap and well
built! <g>
Hi Mark!
I got the following "Tip" from the Compuserve forum last year... it's
exactly what you need:
Date Functions Tip…
Tip Title: Using Date functions to return the date of the last
nominated weekday of any month.
Question: How do I work out what the date is of the last Friday of any
given month so I can include it as criteria in my report query ?
Answer: Create a function that will calculate the date for you by
accepting the name of the month and the desired cut-off day.
Tip level: Intermediate (requires a basic understanding of Access code)
Various types of business require monthly reports to close on a
particular weekday at the end of the month, for example the last Friday
of the month. The problem is that the actual date for the cut-off
could be anything from the 23rd to the 31st day of any given month.
The following function will return a cut-off date for the nominated
month and day of the week. The day of the week is an optional
parameter* when calling the function and will default to Friday if it
is not specified.
*Note. This function was developed in Access 7.0. The Optional
parameter statement and the IsMissing() function is not supported in
Access 2.0.
'----------------------------start of
function---------------------------------
Function MonthCloseDay(vMonth As Variant, Optional sDay As Variant) As
Variant
' vMonth is the month in question. The following formats are
acceptable.
' Jun-97, June/97, 01-Jun-97, 06/01/97
' sDay is the day of the week for which the date is required.
' Pass the day's name - Sun, Mon, Tue, Wed, Thu, Fri, Sat
' This is an option parameter. If nothing is passed, a default will
' be assigned.
'Handle any errors by exiting the function gracefully.
On Error GoTo MonthCloseDayErr
' This is important if you can not guarantee that the
' parameter values will always be correct
' Declare local variables
Dim vDat As Variant, iDay As Integer
' Check if the optional day was passed
If IsMissing(sDay) Then '(Not supported by Access 2.0)
' Set the default day instead
sDay = "FRI"
End If
' Convert passed month to a date
vDat = CVDate(vMonth)
' Calculate the last day of the passed month.
vDat = DateSerial(Year(vDat), Month(vDat) + 1, 0)
' This does two things. First it increases the month by one and
' sets the day to zero. This will change the original 17-Jun-97
' to 00-Jul-97 which Access acknowledges as the last day of june,
30-Jun-97
' Find the index for the passed day. The InStr function will return
' the starting position of the day. Fri = 21
iDay = InStr(1, "Sun,Mon,Tue,Wed,Thu,Fri,Sat", Left$(sDay, 3))
' Make sure it is valid
If iDay = 0 Then GoTo MonthCloseDayErr
' Convert to a number in the range 1 to 7. This will be used to
' compare against Access's WeekDay Function.
iDay = (iDay \ 4) + 1
' Fri = 21 \ 4 = 5 + 1 = 6
' Note the use of Integer Division \ which is faster than normal
' division /
' Find the last occurrence of the week day by subtracting
' one day at a time
Do Until WeekDay(vDat) = iDay
vDat = vDat - 1
Loop
' Return the resulting date
MonthCloseDay = vDat
Exit Function
MonthCloseDayErr:
' An error occurred, return nothing
MonthCloseDay = Null
' In this case Null is used because it can highlight
' that an error did occur by the lack of a date.
Exit Function
End Function
'------------------------------end of
function---------------------------------
Once you've created this function, you can easily include this into a
query's criteria to return all records up to the last monthly close
date.
SELECT tSales.*
FROM tSales
WHERE tSales.TransactDate <= MonthCutOff("Jun-97", "Thu")
You can easily return a monthly range of data by using this function to
identify both the month's start and end dates.
SELECT tSales.*
FROM tSales
WHERE tSales.TransactDate > MonthCutOff("May-97")
AND tSales.TransactDate <= MonthCutOff("Jun-97")
If you have any questions or comments about this tip please ask
Kevin Rollo 100400,734 (at Compuserve).
Good luck!
Al.
Alberto Borges, MD
current year. Any quick ideas?>>
Hi Mark!
I hate to leave you hanging... here is the code of my other letter
reworked to demonstrate the 3rd Sunday of October. Note that it's based on
a clickbutton on a form that calls a function:
Function MonthCloseDay(vMonth As Variant, Optional sDay As Variant) As
Variant
' vMonth is the month in question. The following formats are acceptable->
Jun-97, June/97, 01-Jun-97, 06/01/97
' sDay is the day of the week for which the date is required.
' Pass the day's name - Sun, Mon, Tue, Wed, Thu, Fri, Sat
' This is an option parameter. If nothing is passed, a default will
' be assigned.
'Handle any errors by exiting the function gracefully.
On Error GoTo MonthCloseDayErr
' This is important if you can not guarantee that the
' parameter values will always be correct
' Declare local variables
Dim vDat As Variant, iDay As Integer
' Check if the optional day was passed
If IsMissing(sDay) Then '(Not supported by Access 2.0)
' Set the default day instead
sDay = "SUN"
End If
' Convert passed month to a date
vDat = CDate(vMonth)
vDat = 10 & "/" & DAY(vDat) & "/" & YEAR(vDat)
MsgBox vDat
' Calculate the last day of the passed month.
vDat = DateSerial(YEAR(vDat), Month(vDat) + 1, 0)
' This does two things. First it increases the month by one and
' sets the day to zero. This will change the original 17-Jun-97
' to 00-Jul-97 which Access acknowledges as the last day of june,30-Jun-97
' Find the index for the passed day. The InStr function will return
' the starting position of the day. Sun = 1
iDay = InStr(1, "Sun,Mon,Tue,Wed,Thu,Fri,Sat", Left$(sDay, 3))
' Make sure it is valid
If iDay = 0 Then GoTo MonthCloseDayErr
' Convert to a number in the range 1 to 7. This will be used to
' compare against Access's WeekDay Function.
iDay = (iDay \ 4) + 1
' Sun = 1 \ 4 = 0 + 1 = 1
' Note the use of Integer Division \ which is faster than normal
' division /
' Find the last occurrence of the week day by subtracting
' one day at a time
Do Until WeekDay(vDat) = iDay
vDat = vDat - 1
MsgBox WeekDay(vDat) & "=" & iDay
'MsgBox WeekDay(vDat) & "=" & iDay
Loop
MsgBox "The value of vDat is: " & vDat
' Return the resulting date
MonthCloseDay = vDat - 7
MsgBox "This is the third Sunday of October of this year: " & MonthCloseDay
Exit Function
MonthCloseDayErr:
' An error occurred, return nothing
MonthCloseDay = Null
' In this case Null is used because it can highlight
' that an error did occur by the lack of a date.
Exit Function
End Function
'---------------------------------------------------------------------------
-----------------
Private Sub Command0_Click()
Call MonthCloseDay(DATE)
End Sub
Now, I haven't tried Chuck's workaround... his does seem shorter. Like
they say, there's more than 1 way to skin a cat!
Regards, Al.
Michel Walsh
15 + DateSerial( WantedYear, WantedMonth, 1) -
WeekDay(DateSerial(WantedYear, WantedMonth, 1))
based on the observation that if the first of the month is a Sunday, need to
add 14 to it; if it is a Monday, 13; ...........
Hope it may help,
Vanderghast, Access MVP.
Mark R wrote in message ...
Ken Getz
ma...@mint.net says...
> Need code to return the date for the third Sunday in the October for the
> current year. Any quick ideas?
>
>
which has a function to calculate this, in general. (It also has a ton of
other useful date/time functions, as well.) To calculate this specific
date, you could use:
? dhNthWeekDay(DateSerial(Year(Date), 10, 1), 3, vbSunday)
-- Ken
========
Function dhNthWeekday(dtmDate As Date, intN As Integer, _
intDOW As Integer) As Date
' Find the date of the specified day within the month. For
' example, retrieve the 3rd Tuesday's date.
' From "VBA Developer's Handbook"
' by Ken Getz and Mike Gilbert
' Copyright 1997; Sybex, Inc. All rights reserved.
' In:
' dtmDate:
' Starting date for the search. If this isn't the first
' day of the month, the code moves back to the first.
' intN:
' Number of the specific day, within the month. If larger
' than there are days of the specified type in the month,
' return the date of the requested day anyway. If you ask for
' the 10th Monday, the code will just find the first Monday
' in the specified month, and then add 10 weeks to that date.
' intDOW:
' Day of the week to seek.
' Out:
' Return Value:
' The date of the nth specified day after the first day of the
' the specified month.
' Example:
' dhNthWeekday(#5/5/97#, 3, 3) returns the third Tuesday in 5/97,
' that is, #5/20/97#.
Dim dtmTemp As Date
If (intDOW < vbSunday Or intDOW > vbSaturday) _
Or (intN < 1) Then
' Invalid parameter values. Just
' return the passed-in date.
dhNthWeekday = dtmDate
Exit Function
End If
' Get the first of the month.
dtmTemp = DateSerial(Year(dtmDate), Month(dtmDate), 1)
' Get to the first intDOW in the month.
Do While WeekDay(dtmTemp) <> intDOW
dtmTemp = dtmTemp + 1
Loop
' Now you've found the first intDOW in the month.
' Just add 7 for each intN after that.
dhNthWeekday = dtmTemp + ((intN - 1) * 7)
End Function
Michel Walsh
If we define the first week of a month as the week having at least a day in
it, the third Sunday can fall on the fourth week. The formula I gave returns
the Sunday on the third week. To get the third Sunday, meaning two Sundays
have previously occurred on that month, use instead:
Choose(Weekday(DateSerial(WY, WM, 1)), 14, 20, 19, 18, 17, 16, 15) +
DateSerial(WY,WM, 1)
where WY is the wanted year and WM the wanted month. The observation is
still based on an offset to the first of the month, offset computed (here,
chosen is more appropriate) versus the weekday of the first of the month.
Al Borges
Sheesh... I should have picked that one out (I have 2 copies of your
book- one at home and another at my office- it's great, BTW). That sure is
the shortest method- it's a keeper. AL.
Ken Getz
> Sheesh... I should have picked that one out (I have 2 copies of your
> book- one at home and another at my office- it's great, BTW). That sure is
> the shortest method- it's a keeper. AL.
>
>
I've seen a bunch of versions of this (one or two were posted here) but
they always end up looooooping way too many times. This one has only a
little loop (and I keep thinking I could get rid of that one if I just
thought about it a little more.)
-- Ken
Terry Kreft
The following doesn't loop at all and from my quick testing seems to work
fine.
Function DOM(MonthDate As Date, Num As Integer, DOW As Integer) As Date
Dim dteFirstday As Date
Dim dteLastday As Date
Dim dteTestday As Date
Dim NumDiff As Integer
dteFirstday = DateSerial(Year(MonthDate), Month(MonthDate), 1)
dteLastday = DateAdd("m", 1, dteFirstday) - 1
NumDiff = (DatePart("W", dteFirstday) - 6 + DOW)
dteTestday = dteFirstday + NumDiff
NumDiff = 7 * (Num + (NumDiff = Abs(NumDiff)))
dteTestday = dteTestday + NumDiff
If dteTestday <= dteLastday Then
DOM = dteTestday
End If
End Function
Ken Getz <ke...@nospam.please> wrote in message
news:MPG.10d986174...@msnews.microsoft.com...
Ken Getz
> The following doesn't loop at all and from my quick testing seems to work
> fine.
>
>
I've got. -- Ken
Alberto Borges, MD
WeekDay(DateSerial(WantedYear, WantedMonth, 1))
based on the observation that if the first of the month is a Sunday, need to
add 14 to it; if it is a Monday, 13; ...........>>
Hi Michel!
The problem is that on the formula that I picked up from Compuserve, the
calculations are literally done ass-backwards... ie from the last day of the
month down. I actually thought that the posted formula worked so well, that
last night I incorporated it into one of my databases (ie to pick up on that
elusive 3rd Thursday of the month meeting!). The only problem was that this
month (12/98), for example, has 5 Thursdays! I had to place in a
calculation that went something like (it's in my laptop computer...) If day
of month < 20 then subtract 7 Else subtract 14 from the calculated date (ie
compensate for the extra Thursday) End If.
I see that Terry just came up with a non-looping version... wow, this is
turning out to be a dissertation! <g> I've put them all into my little
library, as it'll come up again in the future.
Regards, Al.
Weyerhaeuser
displayDate = NthWeekDay("10/1/98",3,vbSunday)
The Code => no loops - very quick
Ken - if you use this in your book I want credit for it. ;-)
SMDavis
====================================
Public Function NthWeekDay(MonthDate As Date, intNum As Integer, DOW As
Integer) As Date
Dim dtmFirstDay As Date
Dim dtmTestDay As Date
Dim dtmReturnDay As Date
Dim intFirstWkDay As Integer
dtmFirstDay = DateSerial(Year(MonthDate), Month(MonthDate), 1)
intFirstWkDay = WeekDay(dtmFirstDay)
If intFirstWkDay = DOW Then
'nothing
ElseIf intFirstWkDay > DOW Then
dtmTestDay = DateAdd("d", (DOW + 7 - intFirstWkDay), dtmFirstDay)
ElseIf intFirstWkDay < DOW Then
dtmTestDay = DateAdd("d", (DOW - intFirstWkDay), dtmFirstDay)
End If
dtmReturnDay = DateAdd("d", ((intNum - 1) * 7), dtmTestDay)
NthWeekDay = dtmReturnDay
End Function
====================================
Mark R <ma...@mint.net> wrote in article
<O5Z4tgvI#GA....@uppssnewspub04.moswest.msn.net>...
Michael A. Cross
The loop is a Math quiz, so I gave it to my Daughter (16 year old math
wiz....)
Her answer 1 line no loops, using your function as a leadin after the
quick error check....
dhNthWeekday = ((DateSerial(Year(dtmDate), Month(dtmDate), 1)) + ((7 -
WeekDay(DateSerial(Year(dtmDate), Month(dtmDate), 1))) + intDOW) Mod 7)
+ ((intN - 1) * 7)
...I know it a long one, this is how she explains it...
'Take the Modulus of First Date of the Month, (always 1) added to the
the days of the Week, (always 7) subtracting the WeekDay of the First
day of the month, added to the day of week asked for, divide by 7.
Then add the remaining weeks.
'(1 of Month + ((7 - WeekDay(1st of Month) + intDOW) Mod 7) + ((intN -
1) * 7)
...
Teenagers...
PS. If there something missing in the explanation. Then its Dad
fault...
Hans-Chr. Francke
ldate= DateSerial(Year(aDate), Month(aDate), "01")
ldate = DateAdd("ww", 3-1 , ldate)
DOW = WeekDay(ldate, vbMonday)
If DOW <> 7 Then ldate = DateAdd("d", 7 - DOW , ldate)
Mark R skrev i meldingen ...
Adrian Edwards
third Sunday of the month must be either 15, 16, 17, 18, 19, 20 or 21.
So
15 + (7 - Weekday(1st of month wanted))
If
Monday is the 1st then 3rd Sunday is 15 + (7 - 1) = 21st
Tuesday is the 1st then 3rd Sunday is 15 + (7 - 2) = 20th
Wednesday is the 1st then 3rd Sunday is 15 + (7 - 3) = 19th
Thursday is the 1st then 3rd Sunday is 15 + (7 - 4) = 18th
Friday is the 1st then 3rd Sunday is 15 + (7 - 5) = 17th
Saturday is the 1st then 3rd Sunday is 15 + (7 - 6) = 16th
Sunday is the 1st the 3rd Sunday is 15 + (7 - 0) = 22nd ? - woops the
problem day.
If 1st of Month is Sunday Then
3rd Sunday is 15th
Else
3rd Sunday is 15 + (7 - Weekday(1st of Month wanted))
End If
Adrian
Michael A. Cross wrote in message <367574E7...@ford.com>...