dervive Week Commencing Date from any date?

[Guest]

Hi.

My Database contains a Date/Time field (MyDate) and it would be usefull if I
add a field in my Query that calculates the Week Commencing date for each of
these, using Monday as the Start date

e.g

MyDate = Week Commencing Date
10/10/2005 = 10/10/2005
11/10/2005 = 10/10/2005
18/10/2005 = 17/10/2005
21/10/2005 = 17/10/2005

etc

I've looked through the site but can find nothing on this.

Hoping someone can help.

 

[Guest]

Matt,

Check out "rogersaccesslibrary.com". On this page,

http://rogersaccesslibrary.com/download3.asp?SampleName=DateStuff.mdb

, there is a mdb with many examples of date functions.

Check out the function: Function FirstofWorkWeek2(vdate As Date)

Note that the dates are in mm/dd/yyy format.

HTH

 

[Guest]

Allen browne's page on date formats might also help

http://allenbrowne.com/ser-36.html

 

[Guest]

Cheers, ended up using

WeekCom: [ReceiptDate]+1-Weekday([ReceiptDate]+6)

which seems to work well.