How to determine sunday and saturday dates?

[Guest]

How can i derive the sunday and saturday dates of a week based on a specific
date?

i.e. if my specific date is 07/03/2007, how via a functions can i determine
the suday date (07/01/2007) and the saturday date (07/07/02007) for the seek
07/03/2007 falls in?

Thanks in advance

 

[Douglas J. Steele]

You can tell the day of the week using the Weekday function. If you use it
with the second (optional) parameter of vbSaturday to have it count Saturday
as day 1, Sunday as day 2 and so on, you can get the previous Saturday for
any date using:

DateAdd("d", -Weekday(Date, vbSaturday) + 1, Date)

and the date of the previous Sunday using

DateAdd("d", -Weekday(Date, vbSaturday) + 2, Date)

If you're using this in a query, replace vbSaturday with its value, 7.
(Queries don't know anything about intrinsic VBA constants)

 

[Jamie Collins]

How can i derive the sunday and saturday dates of a week based on a specificdate?

i.e. if my specificdateis 07/03/2007, how via a functions can i determine
the sudaydate(07/01/2007) and the saturdaydate(07/07/02007) for the seek
07/03/2007 falls in?

Current week beginning Sunday (if run on Sunday will return the
current date):
SELECT DATEADD('WW', DATEDIFF('WW', #1990-01-07 00:00:00#, NOW()),
#1990-01-07 00:00:00#)

Current week beginning Sunday (if run on Sunday will return the
previous Sunday):
SELECT DATEADD('D', (DATEDIFF('D', #1990-01-08 00:00:00#, NOW()) \ 7)
* 7, #1990-01-07 00:00:00#)

Jamie.

--