First Weds of Month...

[dweekley]

I am having trouble creating criteria. We have company meeting the first Weds of each month. I am trying to only display lets say First Weds. of June thru the First Thurs. of May. This would ofcourse roll on to July thru June once the June date passes.



Any help would be greatly appreciated.
Thanks
David

[maximus]

The Following Function will calculate the first Wednesday of the current Month of the Year:

Function FirstWedOfMonth() As Date
For x = 0 To 6
If Weekday(DateSerial(Year(Date), Month(Date), 1) + x, vbUseSystemDayOfWeek) = vbWednesday Then
FirstWedOfMonth = DateSerial(Year(Date), Month(Date), 1) + x
End If
Next x
End Function


if you want to specify a date and want to calculate the First Wednesday for that Month of the Year use this code:

Option Compare Database
Dim MyDate As Date
Function FirstWedOfMonth(MyDate) As Date
If IsNull(MyDate) Then
Exit Function
Else
For x = 0 To 6
If Weekday(DateSerial(Year(MyDate), Month(MyDate), 1) + x, vbUseSystemDayOfWeek) = vbWednesday Then
FirstWedOfMonth = DateSerial(Year(MyDate), Month(MyDate), 1) + x
End If
Next x
End If
End Function

[dweekley]

Thanks for the quick response. When I mentioned criteria, I will be only running this in the form of a query. Not sure if that script can run in a query?

David

[Access_Blaster]

Thanks for the quick response. When I mentioned criteria, I will be only running this in the form of a query. Not sure if that script can run in a query?

David

You paste the code maximus gave you into a new function. Then create a new query that calls the function's name, in this case.

FirstWedOfMonth

Also be careful what you name the query, dont use the same name as the function.

[maximus]

Thanks Access_Blaster!!!!.

[Access_Blaster]

You are welcome.