Group by week

[Alex]

I have a user form where a user types a date. I then need to group and count
records in my table for the week of the date the user picked plus the past 3
weeks. My weeks are Monday - Sunday. For example, if a user picked May 20,
2008, week 4 is Monday, May 19 - May 25, week 3 is Monday, May 12 - May 18,
week 2 is Monday, May 5 - May 11 and week 1 is Monday, April 29 - May 4. I
think I use DatePart, but I can't quite get it to work the right way. Thanks
in advance for your help.

 

[Jerry Whittle]

Something like below in the criteria of the date field will give you the days.

Between [TheDateField] - DatePart("w", [TheDateField], vbMonday) + 7
AND [TheDateField] - DatePart("w", [TheDateField], vbMonday) - 20

There will be problems if the date field contains times also.

The next problem is grouping by the Week. You could use the DatePart
function. Just make sure to use vbMonday with it. Also what happens in the
beginning of January when you can have weeks 51, 52, 53 showing up? What is
the first day of the year for your purposes?

 

[Alex]

I can't get this to work at all. I can't even get Test: DatePart("w",[Date
Fnd],vbMonday) to work. I get an 'enter parameter value error'

Something like below in the criteria of the date field will give you the days.

Between [TheDateField] - DatePart("w", [TheDateField], vbMonday) + 7
AND [TheDateField] - DatePart("w", [TheDateField], vbMonday) - 20

There will be problems if the date field contains times also.

The next problem is grouping by the Week. You could use the DatePart
function. Just make sure to use vbMonday with it. Also what happens in the
beginning of January when you can have weeks 51, 52, 53 showing up? What is
the first day of the year for your purposes?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a user form where a user types a date. I then need to group and count
records in my table for the week of the date the user picked plus the past 3
weeks. My weeks are Monday - Sunday. For example, if a user picked May 20,
2008, week 4 is Monday, May 19 - May 25, week 3 is Monday, May 12 - May 18,
week 2 is Monday, May 5 - May 11 and week 1 is Monday, April 29 - May 4. I
think I use DatePart, but I can't quite get it to work the right way. Thanks
in advance for your help.

 

[Jerry Whittle]

My bad! vbMonday won't work in a query. Replace vbMonday with 2 in your
test.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I can't get this to work at all. I can't even get Test: DatePart("w",[Date
Fnd],vbMonday) to work. I get an 'enter parameter value error'

Something like below in the criteria of the date field will give you the days.

Between [TheDateField] - DatePart("w", [TheDateField], vbMonday) + 7
AND [TheDateField] - DatePart("w", [TheDateField], vbMonday) - 20

There will be problems if the date field contains times also.

The next problem is grouping by the Week. You could use the DatePart
function. Just make sure to use vbMonday with it. Also what happens in the
beginning of January when you can have weeks 51, 52, 53 showing up? What is
the first day of the year for your purposes?
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a user form where a user types a date. I then need to group and count
records in my table for the week of the date the user picked plus the past 3
weeks. My weeks are Monday - Sunday. For example, if a user picked May 20,
2008, week 4 is Monday, May 19 - May 25, week 3 is Monday, May 12 - May 18,
week 2 is Monday, May 5 - May 11 and week 1 is Monday, April 29 - May 4. I
think I use DatePart, but I can't quite get it to work the right way. Thanks
in advance for your help.