How to reference all dates to a "Monday"

[Darrell Childress]

I have a table where employees enter the "StartDate" that they are going
to begin working on a specific job. The date could be any day of the
week (Monday, Tuesday, Wed, etc). I need to produce a report that groups
all jobs with a StartDate within the same week. For example, if an
employee enters 8/26/09 as a "StartDate", I want the report to group
that job within the week of 8/24/09 (a Monday). Even if they enter
8/28/09 (a Friday), I would want that grouped with all other jobs within
that week of 8/24/09.
Thanks for any help with this,
Darrell

 

[vanderghast]

StartDate + Choose(DatePart("w", StartDate), +1, 0, -1, -2, -3, -4, +2)


will add one day to startDate if it is a Sunday, +2 if it is a
Saturday, -2 days if it is a Wednesday, etc. DatePart("w", someDate)
returns a number (1=Sunday, ... 7 = Saturday) and Choose select the number
of day to add (with a negative sign, that will be a subtraction) to adjust
to the desired Monday.


Vanderghast, Access MVP

 

[Ron2006]

Another way

controldatename = startdate - datepart("w",startdate) + 2

in otherwords this is startdate minus the number of the day of the
week (sunday is 1 and saturday is 7) plus 2 to make it the monday of
the week in which the date exists.

On a monday this would be monday minus 2 and then plus two to make it
monday's date.
On a friday this would be friday minus 6 to give you the previous
Saturday and then add 2 to make it monday's date.

Ron

 

[John Spencer]

One more way is to use this expression

DateAdd("d",1-Weekday(\StartDate,2),StartDate)

If you want to base the first day of the week on Sunday then change
WeekDay(StartDate,2) to WeekDay(StartDate,1)

You can define other days of the week as the start of the week using the
numbers 1 to 7.



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

 

[Darrell Childress]

Can't think you guys enough for the info. All three of these solutions
make sense and I will take a look at which seems to fit better. This
GREATLY simplifies what I'm trying to accomplish.