current week from start and end date 2

[kwor]

I have a table with records that include a startdate and enddate field. The stardate and enddate for a record could be the same or differ by days, weeks or months.

I have a query that will retrieve all records where the current day is on or between the startdate and enddate i.e. startdate<=Date() AND enddate>=Date()

I also require a query that will retrieve all records for the current week. Can anyone give me some advice on how to achieve this?

 

[r937]

two questions:

how do you define your week? sunday to saturday?

how do you want to handle partial overlaps? let's say this week is april 11 through 17, how do you want to handle the following rows

start april 10 end april 11
start april 13 end april 14
start april 15 end april 19
start april 17 end april 19
start april 10 end april 19

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[PHV]

SELECT ...
FROM ...
WHERE 100*Year(Now())+Format(Now(),"ww") Between 100*Year(startdate)+Format(startdate,"ww") And 100*Year(enddate)+Format(enddate,"ww")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kwor]

Thanks for the replies.
I had actually worked it out by a different method but PHV's method is far more elegant than mine. Thanks.

My method was to calculate the start of week and end of week in unbound controls on the form that called the query

SOW = Date - Weekday(Date) + 1
EOW = Date - Weekday(Date) + 7

I then used those values as parameters in the query:

WHERE (((tblLeave.StartDate)<=[Forms]![frmReports]![EOW]) AND ((tblLeave.EndDate)>=[Forms]![frmReports]![SOW]))