Get date for start of week

D

Duncs

I have the following query, which gives me the week number for a date:

SELECT DatePart("ww",[Date_Started],2,1) AS [Week Worked],
tblActionDetails.Worked_Status, Count(tblActionDetails.Worked_Status)
AS CountOfEntries
FROM tblActionDetails INNER JOIN tblStatus ON
tblActionDetails.Worked_Status = tblStatus.Description
GROUP BY DatePart("ww",[Date_Started],2,1),
tblActionDetails.Worked_Status, tblStatus.StatusID
HAVING (((tblStatus.StatusID)<>1));

Date_Started is defined as a date field, and this gives me the week
number in the year that the date falls on. However, what I need to do
now is show the date for the start of that week, based on a Monday
week start. So, for example, week 46 should show a week commencing
date of 09/11/09, week 47 should show a w/c date of 16/11/09 etc.

I'm not sure how this would be achieved, but I'm sure it's got
something to do with the week number and adding the number of days
passed to it...or something like that ;).

Many TIA

Duncs
 
J

John Spencer

DateAdd("d",2-Weekday([Date_Started]),[Date_Started])

or

DateAdd("D",1-Weekday([Date_Started],2),[Date_Started])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
D

Duncs

Cheers John.

Working now.

Duncs

DateAdd("d",2-Weekday([Date_Started]),[Date_Started])

or

DateAdd("D",1-Weekday([Date_Started],2),[Date_Started])

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County


I have the following query, which gives me the week number for a date:
SELECT DatePart("ww",[Date_Started],2,1) AS [Week Worked],
tblActionDetails.Worked_Status, Count(tblActionDetails.Worked_Status)
AS CountOfEntries
FROM tblActionDetails INNER JOIN tblStatus ON
tblActionDetails.Worked_Status = tblStatus.Description
GROUP BY DatePart("ww",[Date_Started],2,1),
tblActionDetails.Worked_Status, tblStatus.StatusID
HAVING (((tblStatus.StatusID)<>1));
Date_Started is defined as a date field, and this gives me the week
number in the year that the date falls on.  However, what I need to do
now is show the date for the start of that week, based on a Monday
week start.  So, for example, week 46 should show a week commencing
date of 09/11/09, week 47 should show a w/c date of 16/11/09 etc.
I'm not sure how this would be achieved, but I'm sure it's got
something to do with the week number and adding the number of days
passed to it...or something like that ;).
Duncs- Hide quoted text -

- Show quoted text -
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top