Question Formula for 'Week Starting Date' (1 Viewer)

[accessaspire219]

Hi,
I want to calculate the week starting based on a given date in an access query. The formula I am currently using is,

Wk_Starting_Date: DateAdd("ww",[POA_ETA_Wk]-1,DateSerial(Year([POA ETA]),1,1))-(Weekday((DateAdd("ww",[POA_ETA_Wk]-1,DateSerial(Year([POA ETA]),1,1))),2)-1)[/SIZE][/FONT]
[FONT=Calibri][SIZE=3]

For my requirement each week starts on a Monday and ends on Sunday. So for example 01/10/2010 should be classified under week starting date of 01/04/2010. The formula above classifies this as week starting date of 01/11/2010. What changes do I need to make to the above formula to achieve this?
Appreciate your help!
Thanks!!!

 

[raskew]

Hi -

Try playing with this. It's based on Northwind's Orders table with records from 1995/1996.

SELECT
    Orders.OrderID
  , Orders.CustomerID
  , Orders.OrderDate
  , Year([enter fystartdate mm/dd/yy]) & "/" & Year([enter fystartdate mm/dd/yy])+1 AS FiscalYear
  , DatePart("ww",[OrderDate],2,2) AS Expr1
FROM
   Orders
WHERE
   (((Orders.OrderDate) Between [enter fystartdate mm/dd/yy] 
AND
   DateAdd("yyyy",1,[enter fystartdate mm/dd/yy])-1))
ORDER BY
   Orders.OrderDate;

HTH - Bob