X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Nth Day Of Week In Month, Access 2007    
post May 30 2015, 08:39 AM

Posts: 2,428
Joined: 12-February 15
From: SW AZ

Over the years I've had a need to determine the Nth (1st, 2nd, 3rd, 4th or Last) Day of the Week of a given month and year. Reviewing the Code Archive I didn't notice any corresponding function, so I'm submitting mine.
'NthDowInMonth ******************************************************
'  the date of the Nth Day of Week In the Month (e.g. First Thursday, Last Monday)
'  DateInMonth (Date) - any date in the desired month
'  Nth (Long) - 1 (or < 2) = 1st, 2= 2nd, 3=3rd, 4=4th, 5 (or >4)=Last
'  Dow (VbDayOfWeek) - the Visual Basic Constant for the dow, e.g. vbSundy
'  Access default firstdayofweek (vbSunday=1)
Function NthDowInMonth(DateInMonth As Date, Nth As Long, Dow As VbDayOfWeek) As Date
   Dim fDate As Date, yDate As Date, zDate As Date, days As Long, zNth As Long
   '//verify Nth is valid, adjust if not
      zNth = IIf(Nth < 2, 1, IIf(Nth > 4, 5, Nth))
   '//1st day of the month
      fDate = DateSerial(year(DateInMonth), month(DateInMonth), 1)
   '//last day in month
      yDate = DateAdd("m", 1, fDate) - 1
   '//calculate days to first weekday in month
      days = Dow - Weekday(fDate)
   '//negative days points to previous month, increment by 7
      If (days < 0) Then days = days + 7
   '//tentative return date
      zDate = fDate + days + (zNth - 1) * 7
   '//decr by 7 if date in next month (adjusts for Last dow)
      If zDate > yDate Then zDate = zDate - 7
   '//the return value
      NthDowInMonth = zDate
End Function
Go to the top of the page

Custom Search
RSSSearch   Top   Lo-Fi    23rd October 2018 - 09:43 PM