# 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.

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.
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,
###### 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.

CIOCommented:
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
Commented:
The title of this question and the question text scope your desired answer as of the week

Did you mean "of the month"?
0
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.

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.
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
CIOCommented:
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
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.
0
CIOCommented:
You need a coffee break. These make no sense:

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

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

Experts Exchange Solution brought to you by