Calculate "week-ending" date

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Mona-ABE - 22 Nov 2005 11:44 GMT

In an Access query, I'd like to have an expression that takes the current day
of the week (Sun through Sat) and returns the date for Saturday (in "Short
Date" format) of that same week. Please advise.

Signature

Thanks!
Mona-ABE

Reply to this Message

Jeff Boyce - 22 Nov 2005 12:11 GMT

One way to approach this would be to get the day number (Sunday = 1, Monday
= 2, ...) of the current date (Date()), subtract that from 7 (days in a
week), then add that many days to the current date (using DateAdd()
function).

Or have I misunderstood?

Signature

Regards

Jeff Boyce
<Office/Access MVP>

> In an Access query, I'd like to have an expression that takes the current day
> of the week (Sun through Sat) and returns the date for Saturday (in "Short
> Date" format) of that same week. Please advise.

Reply to this Message

Mona-ABE - 22 Nov 2005 17:15 GMT

Thanks so much for the tip...it worked great!!!

Signature

Thanks!
Mona-ABE

> One way to approach this would be to get the day number (Sunday = 1, Monday
> = 2, ...) of the current date (Date()), subtract that from 7 (days in a
[quoted text clipped - 7 lines]
> > of the week (Sun through Sat) and returns the date for Saturday (in "Short
> > Date" format) of that same week. Please advise.

Reply to this Message

John Ortt - 22 Nov 2005 12:25 GMT

Try something allong the lines of:

DateAdd("d",(7-Weekday([DATE])),[DATE])

> In an Access query, I'd like to have an expression that takes the current
> day
> of the week (Sun through Sat) and returns the date for Saturday (in "Short
> Date" format) of that same week. Please advise.

Reply to this Message

Mona-ABE - 22 Nov 2005 17:15 GMT

Thanks so much for the tip...it worked great!!!

Signature

Thanks!
Mona-ABE

> Try something allong the lines of:
>
[quoted text clipped - 4 lines]
> > of the week (Sun through Sat) and returns the date for Saturday (in "Short
> > Date" format) of that same week. Please advise.

Reply to this Message