Retrieving Date at start/end of week

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a report with a grouping level on the week of the year. How would I
get the date at the beginning and end of each week? i.e. If two records I
have contain the dates 11/7/2006 and 11/9/2006, I would want to be able to
get the dates 11/5/2006 and 11/11/2006 as calculated fields which would
pertain to each of those records.
 
You can try using date functions to calcluate the start of the week and end
of the week by trying this.

Given a text box on the form named txtDate with controlsource set to the
value from the record, add a text box named txtWeekStartDate and a text box
named txtWeekEnd date.

Set the control source for txtWeekStartDate to:
=DateValue([txtDate]-Weekday([txtDate])+1)

Set the control source for txtWeekEndDate to:
=DateValue([txtStartDate]+6)

This assumes your week starts on a Sunday and ends 6 days later. You can
adjust the constants according to the dates you want your week start and stop.

Hope this helps...
 

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

Similar Threads

Dates not sorting correctly in Report 9
Date Grouping 2
Number of hours in report 7
Comparing dates 1
Calculating totals on a Crosstab Query 34
Excel DateDif - why does 2007 seem different? 3
Start Date + 7 5
Group by week 2

Back
Top