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
MsgBox "You have schedule on the wrong Monday for the " & Me.HoldCalA & "."
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) _
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
' Zero, none or invalid value for VbDayOfWeek.
Weekday = VBA.Weekday(DateInMonth)
' Validate Occurence.
If Occurrence <= 0 Then
Occurrence = 1
ElseIf Occurrence > 5 Then
Occurrence = 5
' 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)
DateWeekdayInMonth = ResultDate
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