Date manipulation

[Chris]

I need to find a way to write a query that will return the date of the
Friday of four weeks ago. So, if I use today's date (7/30), I should
be returning 7/6 as 4 weeks ago on Friday. Any help is appreciated.

[ruralguy via AccessMonster.com]

Here's some links you might find useful:
A plethora of date functions
http://www.pacificdb.com.au/MVP/Code/Dates.htm
Functions for calculating and for displaying Date/Time values in Access
http://support.microsoft.com/default...en-us%3B210604

Chris wrote:

>I need to find a way to write a query that will return the date of the
Friday of four weeks ago. So, if I use today's date (7/30), I should
be returning 7/6 as 4 weeks ago on Friday. Any help is appreciated.

--
HTH - RuralGuy (RG for short) acXP WinXP Pro
Please post back to this forum so all may benefit.

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200707/1

[Bob Quintal]

Chris <co*****@gmail.comwrote in
news:11**********************@x35g2000prf.googlegr oups.com:

I need to find a way to write a query that will return the
date of the Friday of four weeks ago. So, if I use today's
date (7/30), I should be returning 7/6 as 4 weeks ago on
Friday. Any help is appreciated.

Access has powerful date mathematics functions.

DateAdd() does what it says, adds a number of periods e.g. days,
weeks months, to a date, or substracts them
Weekday() returns a number from 1 to 7, from Sunday to Saturday.

First we need next Friday:
Dateadd("d", 7- weekday(date(),vbSaturday ),date())

Then we subtract 4 or 5 weeks:(not sure if you mean 4 weeks ago
from last or next friday.

DateAdd("ww",-4, Dateadd("d", 7- weekday(date(),vbSaturday
),date()))

Paste that in the field row of the query designer, change the
expr1: to a meaningful name, and test it.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com