last Friday of each month (group on)

[Guest]

Using Access 2000, I have a report based on a query/table with a date field.
I would like my report to group on the last Friday of each month. Any ideas
on what kind of formula I could use for that? Thanks.

 

[Ken Snell [MVP]]

The following expression will return the date of the last Friday of the
month in which the MyDate is (for example, if MyDate is March 18, 2005, then
the expression returns March 25, 2005 as the last Friday of the month of
March 2005):

DateAdd("d",1-DatePart("w",DateSerial(Year(MyDate),Month(MyDate)+1,0),6),DateSerial(Year(MyDate),Month(MyDate)+1,0))