How do I extract calendar week date ranges given a month?

M

M Skabialka

I need a report that takes a calendar month, and breaks down the data by
calendar week from Sun to Sat
e.g. for Jan 04, the weeks would cover
1-3, 4-10, 11-17, 18-44, 25-31 Jan

However I want the user to be able to select a month number and year, and
have the report figure out what days each week would cover, so Feb 04 would
select 1-7, 8-14, 15-21, 22-28, 29-29 Feb as the week ranges.

Right now I am hard coding the query to use:
Wk1: Sum(IIf([tblHoursEntry].[DayWorked] Between #1/1/04# And
#1/3/04#,[HoursWorked],0))
Wk2: Sum(IIf([tblHoursEntry].[DayWorked] Between #1/4/04# And
#1/10/04#,[HoursWorked],0))
etc

I need to be able to extract the beginning and end of the selected month
And I need the hard coded dates replaced with some kind of formula to
extract the range of dates for each of the weeks in that month.

So far, using MonthSelected = 1 - 12, and YearSelected = 2004, 2005, etc
from a form,

for Day 1 of the month:
[FirstDay] = Format(Format((forms!frmHoursReports!MonthSelected &
"/01/00"),"m") & ", " & forms!frmHoursReports!YearSelected ,"m/\1/yy")

and for the last day of the month
[LastDay] =
Format(DateAdd("d",-1,DateAdd("m",1,Format(Format((forms!frmHoursReports!Mon
thSelected & "/01/00"),"m") & ", " & forms!frmHoursReports!YearSelected
,"m/\1/yy"))),"m/d/yy")


but these are so cumbersome and don't even start to look at the week ranges!

There have to be date functions I am not aware of that will do this task for
me, breaking up the month into calendar weeks!

Thanks for any help,
Mich
 
Top