Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

current week from start and end date 2

Status
Not open for further replies.

kwor

MIS
Dec 10, 2008
35
AU
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?
 
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
 
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
 
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]))
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top