Convert Week # to Date

[Sheryl Bradley]

I have the following query, which gives me a sum of List
Price by Area Group (thanks to some help from an earlier
post!)

SELECT DatePart("ww",ListDate), AreaNameGroup, Sum
(ListPrice)
FROM NewListings
GROUP BY DatePart("ww",ListDate), AreaNameGroup;

I want to do 2 things:

1) Change the first day of the week (comparable
functionality to "Set Datefirst" in SQL Server).

2) Improve the query to return the first day of the week
for the week # generated by DatePart("ww", ListDate). In
other words, if I'm looking at data for Week "25", I'd
like to my query to show that the data is for "Week
beginning June 2" (or whatever the week 25 start date
is). I don't think my users want to figure out what date
Week 25 starts on! smile!

I have tried Access Help, and SQL Server Books Online,
but I'm just really rusty at this!

Thanks so much!

 

[Wayne Morgan]

1) DatePart("ww", ListDate, vbMonday) will make Monday the first day of the
week.

2) DateAdd("d", -Weekday(ListDate, vbMonday)+1, ListDate)