[AccessD] Is there a way to determine months with 31 days that start on tuesday?

[Gustav Brock]

Hi David

That's a bit tricky.

You could use the function below to find the first Tuesday of the month of the date in question, and then the first Tuesday of the next month. If your date falls between these two dates, it belongs to "a week of the month". If not, it is of the month before or after.
When settled, calculate the count of weeks (1 + DateDiff( "ww", ..)) between the found first Tuesday and the date in question.

<code>
Public Function DateMonthWeekday( _
  Optional ByVal bytWeekdayOccurrence As Byte, _
  Optional ByVal datDateInMonth As Date, _
  Optional ByVal bytWeekday As Byte) _
  As Date
  
  Const cintDaysInWeek  As Integer = 7
  Dim intDayOffset      As Integer
  Dim datMonthFirst     As Date
  Dim datWeekday        As Date
  
' Calculates occurrence of bytWeekday of the month of datDateInMonth.
' If bytWeekdayOccurrence is 0 the first occurrence of bytWeekday of the month is assumed.
' If bytWeekdayOccurrence is 5 or anything else different from 0 to 4, the
' last occurrence of bytWeekday of the month is assumed.
' If no date for the month is supplied, today's date is used.
' If bytWeekday is not the Value of a weekday, the weekday of datDateInMonth is used.
' Returns the date as a date/time Value.
'
' 2008-09-12, Cactus Data ApS, CPH.
' 2009-12-10. Calculation of last occurrence changed to call of DateMonthLastWeekday().

  If datDateInMonth = 0 Then
    ' No date is specified.
    datDateInMonth = Date
  End If

  ' Validate bytWeekday.
  Select Case bytWeekday
    Case _
      vbMonday, _
      vbTuesday, _
      vbWednesday, _
      vbThursday, _
      vbFriday, _
      vbSaturday, _
      vbSunday
    Case Else
      ' Zero, none or invalid value for weekday.
      bytWeekday = Weekday(datDateInMonth, vbSunday)
  End Select
  
  ' Validate bytWeekdayOccurrence.
  If bytWeekdayOccurrence = 0 Then
    bytWeekdayOccurrence = 1
  End If
  
  Select Case bytWeekdayOccurrence
    Case 1, 2, 3, 4
      datMonthFirst = DateSerial(Year(datDateInMonth), Month(datDateInMonth), 1)
      ' Find offset of bytWeekday from first day of month.
      intDayOffset = (bytWeekday - Weekday(datMonthFirst, vbSunday) + cintDaysInWeek) Mod cintDaysInWeek
      ' Find offset for occurence no. of bytWeekday from first day of month.
      intDayOffset = intDayOffset + cintDaysInWeek * (bytWeekdayOccurrence - 1)
      datWeekday = DateAdd("d", intDayOffset, datMonthFirst)
    Case Else
      datWeekday = DateMonthLastWeekday(datDateInMonth, bytWeekday)
  End Select
  
  DateMonthWeekday = datWeekday

End Function

Public Function DateMonthLastWeekday( _
  Optional ByVal datDateInMonth As Date, _
  Optional ByVal bytWeekday As Byte) _
  As Date

' Calculates last occurrence of bytWeekday of the month of datDateInMonth.
' If no date for the month is supplied, today's date is used.
' If no weekday is supplied, the weekday of the supplied date is used.
' Returns the date as a date/time Value.
'
' 2007-01-25, Cactus Data ApS, CPH.
' 2009-12-10. Calculation of bytDayDiff simplified.
'             Parameters made optional.
  
  Dim datLastDay As Date
  Dim bytDayDiff As Byte
  
  ' No specific error handling.
  On Error Resume Next
  
  If datDateInMonth = 0 Then
    datDateInMonth = Date
  End If
  
  ' Validate bytWeekday.
  Select Case bytWeekday
    Case _
      vbMonday, _
      vbTuesday, _
      vbWednesday, _
      vbThursday, _
      vbFriday, _
      vbSaturday, _
      vbSunday
    Case Else
      ' Zero, none or invalid value for weekday.
      bytWeekday = Weekday(datDateInMonth, vbSunday)
  End Select
  
  ' Find last day of the month of datDateInMonth.
  datLastDay = DateSerial(Year(datDateInMonth), Month(datDateInMonth) + 1, 0)
  
  ' Determine number of days between last day of month and last lngWeekday of month
  ' by assuming lngWeekday being the first day of a week.
  bytDayDiff = Weekday(datLastDay, bytWeekday) - 1
  
  ' Calculate closest preceding weekday of the last day including this.
  DateMonthLastWeekday = DateAdd("d", -bytDayDiff, datLastDay)
  
End Function
</code>

/gustav
________________________________________
Fra: AccessD <accessd-bounces at databaseadvisors.com> på vegne af David McAfee <davidmcafee at gmail.com>
Sendt: 28. oktober 2015 18:16
Til: Access Developers discussion and problem solving
Emne: [AccessD] Is there a way to determine months with 31 days that    start on tuesday?

I'm trying to determine a given week number for a given month in order to
auto-run a certain process in Access.

If a given week has four or more business days (M-F) in a week, we count it.
We only have processes for weeks 1-4.

I have brought up the fact that there are months with five, four or more
days weeks, such as December 2015.

December 2015 doesn't matter to my company as the 5th week, we are shut
down, so no 5th week process is needed.

I was trying to determine, via code, if there was a way to list any month
like this for the next three years.

I don't know which is easier to determine (1,2 or 3):
1. All months with 31 days that start (1st) on Tuesday
2. Months with 5 weeks of four or more business days (M-F)
3. Some built in method?

Does anyone have an idea?

Thanks,
David