Calculating 2nd Thursday after 1st Tuesday in a Month

[jriechers]

I need to create a database that will be used to schedule patches. Many of our patches happen on the 1st Saturday of the month, but some happen on the 2nd Thursday after the 1st Tuesday every month.

Ive been attempting to use the DATEPART and WEEKDAY functions but without success. Figuring out the 1st Saturday shouldnt be to hard, even if I havent managed to figure that out just yet, but the other is beyond my experience.

[PianoMan64]

I need to create a database that will be used to schedule patches. Many of our patches happen on the 1st Saturday of the month, but some happen on the 2nd Thursday after the 1st Tuesday every month.

Ive been attempting to use the DATEPART and WEEKDAY functions but without success. Figuring out the 1st Saturday shouldnt be to hard, even if I havent managed to figure that out just yet, but the other is beyond my experience.

Here's your answer:

 
Function FirstSatOfMonth() As Date
 
    For x = 0 To 6
        If Weekday(DateSerial(Year(Date), Month(Date), 1) + x, vbUseSystemDayOfWeek) = vbSaturday Then
            FirstSatOfMonth = DateSerial(Year(Date), Month(Date), 1) + x
        End If
    Next x
End Function
 
Function SecondThursdayafterFirstTuesday() As Date
    For x = 0 To 6
        If Weekday(DateSerial(Year(Date), Month(Date), 1) + x, vbUseSystemDayOfWeek) = vbTuesday Then
            SecondThursdayafterFirstTuesday = DateSerial(Year(Date), Month(Date), 1) + x + 9
        End If
    Next x
End Function
 
 

[jriechers]

Wow! Thanks for the quick response. Its midnight here, didnt think anyone would reply until tomorrow morning. You rock!

[jriechers]

I used the information you gave me to create 33 functions. They all work perfect now, but Ive run into another problem.

The following is a quick example of what I am trying to do:

---
Private Sub MonthCombo_AfterUpdate(Cancel As Integer)

[test] = First_Saturday()

End Sub
---

Basically when the user changes the month in the form header it will update all the patch windows using their particular function.

First_Saturday() is in a module named DateCalculation.

Originally I had attached each function directly to related textbox as default value which worked just fine until I figured it need the flexibility of changing the month value.

I had changed a function to replace Month(Date) with [Form]![test]![MonthCombo] which works, but only as the form loads. I need the data to update if the month is changed.

This is the following error I get as the AfterUpdate runs...

The expression After Update you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name.

[PianoMan64]

I used the information you gave me to create 33 functions. They all work perfect now, but Ive run into another problem.

The following is a quick example of what I am trying to do:

---
Private Sub MonthCombo_AfterUpdate(Cancel As Integer)

[test] = First_Saturday()

End Sub
---

Basically when the user changes the month in the form header it will update all the patch windows using their particular function.

First_Saturday() is in a module named DateCalculation.

Originally I had attached each function directly to related textbox as default value which worked just fine until I figured it need the flexibility of changing the month value.

I had changed a function to replace Month(Date) with [Form]![test]![MonthCombo] which works, but only as the form loads. I need the data to update if the month is changed.

This is the following error I get as the AfterUpdate runs...

The expression After Update you entered as the event property setting produced the following error: Procedure declaration does not match description of event or procedure having the same name.

The sub routine that you sent, I'm assuming the [Test] is a control on the current form this sub is in?

If so, simply do the following:

 
Private Sub MonthCombo_BeforeUpdate(Cancel As Integer)
 
  me.Test = First_Saturday()
 
End Sub
 

Hope that helps,

Joe P.

p.s. The AfterUpdate Event doesn't support (Cancel as Integer). That's why you were getting the error.