First & Third Monday of the week

urjudo
urjudo used Ask the Experts™
on
Hi Experts,
I had asked a question about First Monday & Third Monday of the week couple week ago and Expert Gustav Brock gave me the Public Function which works but when I tried to place to another form, it does not work as it suppose.  I know I did something wrong, but did not what I miss.  below is my code on a datefield:
Private Sub PDFIRSTCRTDATE_Exit(Cancel As Integer)
Dim DateInMonth As Date
DateInMonth = Date

    If Me.HoldCalA = "Cal 24" Or Me.HoldCalA = "Cal 42" Or Me.HoldCalA = "Cal 54" Then
       If Me.PDFIRSTCRTDATE = DateWeekdayInMonth(DateInMonth, 1, vbMonday) Or Me.PDFIRSTCRTDATE = DateWeekdayInMonth(DateInMonth, 3, vbMonday) Then
       Else
          MsgBox "You have schedule on the wrong Monday for the " & Me.HoldCalA & "."
       End If
    End If
  End Sub



Here is the Public Function from Gustav Brock:
' Calculates the date of the occurrence of Weekday in the month of DateInMonth.
'
' If Occurrence is 0 or negative, the first occurrence of Weekday in the month is assumed.
' If Occurrence is 5 or larger, the last occurrence of Weekday in the month is assumed.
'
' If Weekday is invalid or not specified, the weekday of DateInMonth is used.
'
' 2016-06-09. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateWeekdayInMonth( _
    ByVal DateInMonth As Date, _
    Optional ByVal Occurrence As Integer, _
    Optional ByVal Weekday As VbDayOfWeek = -1) _
    As Date
   
    Const DaysInWeek    As Integer = 7
   
    Dim Offset          As Integer
    Dim Month           As Integer
    Dim Year            As Integer
    Dim ResultDate      As Date
   
    ' Validate Weekday.
    Select Case Weekday
        Case _
            vbMonday, _
            vbTuesday, _
            vbWednesday, _
            vbThursday, _
            vbFriday, _
            vbSaturday, _
            vbSunday
        Case Else
            ' Zero, none or invalid value for VbDayOfWeek.
            Weekday = VBA.Weekday(DateInMonth)
    End Select
   
    ' Validate Occurence.
    If Occurrence <= 0 Then
        Occurrence = 1
    ElseIf Occurrence > 5 Then
        Occurrence = 5
    End If
   
    ' Start date.
    Month = VBA.Month(DateInMonth)
    Year = VBA.Year(DateInMonth)
    ResultDate = DateSerial(Year, Month, 1)
   
    ' Find offset of Weekday from first day of month.
    Offset = DaysInWeek * (Occurrence - 1) + (Weekday - VBA.Weekday(ResultDate) + DaysInWeek) Mod DaysInWeek
    ' Calculate result date.
    ResultDate = DateAdd("d", Offset, ResultDate)
   
    If Occurrence = 5 Then
        ' The latest occurrency of Weekday is requested.
        ' Check if there really is a fifth occurrence of Weekday in this month.
        If VBA.Month(ResultDate) <> Month Then
            ' There are only four occurrencies of Weekday in this month.
            ' Return the fourth as the latest.
            ResultDate = DateAdd("d", -DaysInWeek, ResultDate)
        End If
    End If
   
    DateWeekdayInMonth = ResultDate
 
End Function

When I entered 8/20/18 or 9/17/18 , the message pops up, it should be pop up the message because both are third Mondays because we schedule ahead of time and even for next year .  any help will be appreciate

Thanks,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
The function will return Mondays in August, if you use Date as DateInMonth.

Use DateAdd("m", 1, Date) if you need a Monday in Septemper.
Top Expert 2014

Commented:
The title of this question and the question text scope your desired answer as of the week

Did you mean "of the month"?

Author

Commented:
I changed the public function to this:
' Calculates the date of the occurrence of Weekday in the month of DateInMonth.
'
' If Occurrence is 0 or negative, the first occurrence of Weekday in the month is assumed.
' If Occurrence is 5 or larger, the last occurrence of Weekday in the month is assumed.
'
' If Weekday is invalid or not specified, the weekday of DateInMonth is used.
'
' 2016-06-09. Gustav Brock, Cactus Data ApS, CPH.
'
Public Function DateWeekdayInMonth( _
    ByVal DateInMonth As Date, _
    Optional ByVal Occurrence As Integer, _
    Optional ByVal Weekday As VbDayOfWeek = -1) _
    As Date
   
    Const DaysInWeek    As Integer = 7
   
    Dim Offset          As Integer
    Dim Month           As Integer
    Dim Year            As Integer
    Dim ResultDate      As Date
   
    ' Validate Weekday.
    Select Case Weekday
        Case _
            vbMonday, _
            vbTuesday, _
            vbWednesday, _
            vbThursday, _
            vbFriday, _
            vbSaturday, _
            vbSunday
        Case Else
            ' Zero, none or invalid value for VbDayOfWeek.
            Weekday = VBA.Weekday(DateInMonth)
    End Select
   
    ' Validate Occurence.
    If Occurrence <= 0 Then
        Occurrence = 1
    ElseIf Occurrence > 5 Then
        Occurrence = 5
    End If
   
    ' Start date.
    Month = VBA.Month(DateInMonth)
    Year = VBA.Year(DateInMonth)
    ResultDate = DateSerial(Year, Month, 1)
   
    ' Find offset of Weekday from first day of month.
    Offset = DaysInWeek * (Occurrence - 1) + (Weekday - VBA.Weekday(ResultDate) + DaysInWeek) Mod DaysInWeek
    ' Calculate result date.
    ResultDate = DateAdd("m", Offset, ResultDate)
   
    If Occurrence = 5 Then
        ' The latest occurrency of Weekday is requested.
        ' Check if there really is a fifth occurrence of Weekday in this month.
        If VBA.Month(ResultDate) <> Month Then
            ' There are only four occurrencies of Weekday in this month.
            ' Return the fourth as the latest.
            ResultDate = DateAdd("m", -DaysInWeek, ResultDate)
        End If
    End If
   
    DateWeekdayInMonth = ResultDate
 
End Function

changed the "d" to "m".  but where I should DateAdd("m", 1, Date) as Gustav Brock suggest.  also my code in the date field does not show any message at all even I entered the fourth Monday of the month.

For aikimark, it's of the month, sorry, typo
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
Oh no. Don't modify the function. It ran perfectly allright.
Please understand what it is doing:

' Calculates the date of the occurrence of Weekday in the month of DateInMonth.

So, pass a date (the DateInMonth parameter) to it that represents the month for which you wish to find a weekday. Thus:

If this month: Date
If previous month: DateAdd("m", -1, Date)
If next month: DateAdd("m", 1, Date)

Author

Commented:
okay, I changed back the Public Function as it was before.  here is my datefield code:
    If Me.HoldCalA = "Cal 24" Or Me.HoldCalA = "Cal 42" Or Me.HoldCalA = "Cal 54" Then
        If Me.PDFIRSTCRTDATE = DateAdd("m", 1, vbMonday) Or Me.PDFIRSTCRTDATE = DateAdd("m", 3, vbMonday) Then
       Else
           MsgBox "You have schedule on the wrong Monday for the " & Me.HoldCalA & "."
        End If
     End If

and this code still no showing any error message.
Most Valuable Expert 2015
Distinguished Expert 2018
Commented:
You need a coffee break. These make no sense:

Me.PDFIRSTCRTDATE = DateAdd("m", 1, vbMonday) Or Me.PDFIRSTCRTDATE = DateAdd("m", 3, vbMonday)

Open in new window

Maybe it should read:

Me.PDFIRSTCRTDATE = DateWeekdayInMonth(Date, 1, vbMonday) Or Me.PDFIRSTCRTDATE = DateWeekdayInMonth(DateAdd("m", 1, Date), 3, vbMonday)

Open in new window

Author

Commented:
Thank you!!!
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
You are welcome!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial