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:

[code=vb]

Function FirstSatOfMonth () As Date

For x = 0 To 6
If Weekday(DateSer ial(Year(Date), Month(Date), 1) + x, vbUseSystemDayO fWeek) = vbSaturday Then
FirstSatOfMonth = DateSerial(Year (Date), Month(Date), 1) + x
End If
Next x
End Function

Function SecondThursdaya fterFirstTuesda y() As Date
For x = 0 To 6
If Weekday(DateSer ial(Year(Date), Month(Date), 1) + x, vbUseSystemDayO fWeek) = vbTuesday Then
SecondThursdaya fterFirstTuesda y = DateSerial(Year (Date), Month(Date), 1) + x + 9
End If
Next x
End Function

[/code]

[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_Afte rUpdate(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_Afte rUpdate(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:

[code=vb]

Private Sub MonthCombo_Befo reUpdate(Cancel As Integer)

me.Test = First_Saturday( )

End Sub
[/code]

Hope that helps,

Joe P.

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