Date functions in a query

[ccordner]

Hi, think this is pretty simple but I can't work it out.



I want two expressions in a query.

One to work out the Sunday before the first day of the month (or the first day of the month if it was a Sunday.

The other to work out the last day of the year.

Any ideas?

Thanks
Chris

[boblarson]

Well, the last day of the year is easy:

LastDay:Format(DateSerial(Year(Date()), 12, 31), "dddd")

As for the Sunday before the first day of the month you can paste this code into a STANDARD Module (name the module something other than the name of the function):

'Code courtesy of Bob Askew (raskew on Access World Forums)
Function GetPrevSunday(dteDate As Date) As Date
    GetPrevSunday = DateAdd("d", -Weekday(dteDate) + 1, dteDate)
End Function

and then call it in your query:
MySunday:GetPrevSunday(DateSerial(Year(date()), Month(Date()),1))