First & Third Monday of the week

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,
urjudoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
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.
0
aikimarkCommented:
The title of this question and the question text scope your desired answer as of the week

Did you mean "of the month"?
0
urjudoAuthor 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
0
Gustav BrockCIOCommented:
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)
0
urjudoAuthor 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.
0
Gustav BrockCIOCommented:
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

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
urjudoAuthor Commented:
Thank you!!!
0
Gustav BrockCIOCommented:
You are welcome!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.