Last Saturday of the Month

[LauraT]

First time needing to work with date criteria in Access 2002 and not being
very successful. Any help would be greatly appreciated.
Our fiscal month ends on the last Saturday of each month. I need to return
that date based on a day in that month.
ShipDay Returns
5/9/05 5/28/05
An example of one of my unsuccessful attempst is
IIf(DatePart("d",ShipDate+12)<7,Month(ShipDate),Month(ShipDate)+1
I even tried to paste the following in a module to try and get the week
number of the month (1,2,3,4)
Option Explicit

Public Function WhichWeek(InDate As Date) As Integer
Dim EnteredDate As Date
EnteredDate = InDate
FirstDateCount = Weekday(GetDate(EnteredDate, "Current", "First"))
If DatePart("d", InDate) <= FirstDateCount Then
WhichWeek = 1
Else
WhichWeek = 1 + Int(DatePart("d", InDate) - FirstDateCount) / 7
End If
End Function

It gets hung on the GetDate and I'm just now trying to learn vba.

Again, any help would be greatly appreciated.
Thank you
Laura

 

[Rick B]

Laura: I just posted an answer that will locate dates between the last
Friday of last moneht and the Last Friday of this month. Perhaps you can
modify the statement to meet your needs...

Between
DateAdd("d",1-DatePart("w",DateSerial(Year(Date()),Month(Date()),0),6),DateS
erial(Year(Date()),Month(Date()),0)) and
DateAdd("d",1-DatePart("w",DateSerial(Year(Date()),Month(Date())+1,0),6),Dat
eSerial(Year(Date()),Month(Date())+1,0))

 

[Douglas J. Steele]

The following function will return the date for a "generic" day of the month
(such as the third Monday of the month):

Function GetDate (WhatYear As Long, _
WhatMonth As Long, _
WhatWeekDay As Long, _
WhatWeekDayOfMonth As Long) As Date

Dim dtmFirstOfMonth As Date

dtmFirstOfMonth = _
DateSerial(WhatYear, WhatMonth, 1)

GetDate = DateAdd("w", _
(WhatWeekDayOfMonth - 1) * 7, _
DateAdd("d", _
(WhatWeekDay - _
Weekday(dtmFirstOfMonth) + 7) Mod 7, _
dtmFirstOfMonth) _
)

End Function

You'd use it as:

dtmLastSaturday = GetDate(Year(Date()), Month(Date()), vbSaturday, 5)

to get the 5th Saturday of this month.

To be sure you've got the last Saturday, check whether the 5th Saturday of
the month is in the same month or not. If it isn't, then check the 4th
Saturday:

dtmLastSaturday = GetDate(Year(Date()), Month(Date()), vbSaturday, 5)
If Month(dtmLastSaturday) <> Month(Date()) Then
dtmLastSaturday = GetDate(Year(Date()), Month(Date()), vbSaturday, 4)
End If