Crosstab Queries by week

[MarieT]

I need to make a crosstab query that has, as a column heading, the date of
the beginning of the week in which the date value in the record falls. I know
how to make the heading be a number representing the week of the year by
using: Format(ShipDate, "ww"), but how do I convert the week digit to an
actual Date reprersenting the beginning of the week the date falls in? For
example, if the ShipDate is March 13, 2008, the heading will be: 2008/03/09
assuming Sunday is the beginning of the week. If ShipDate is March 18, 2008
then the heading will be 2008/03/16.

Any help will be greatly appreciated!

MarieT

 

[Jerry Whittle]

This will get you close. Unfortunately it will set Sunday's to the previous
Sunday. Also I hadn't tested it for dates with times. Nor did I convert it to
the format you needed.

FirstDayOfWeek: [ShipDate]-DatePart("w",[wa_date],2)

You may need to put this in a query then base the crosstab query on the
first query.

 

[John Spencer]

Try the following

DateAdd("d", 1 - Weekday([ShipDate]),[ShipDate])

as the expression to get the first day of the week.



'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================