week numbers

[Guest]

I have an access database where I am looking to calculated quantities that
fall within particular date periods, grouping by month is not a problem but
when grouping by week (on the report group function) some data does not
appear in the same week number as what should be on a calendar. If there any
reason why this happens and any settings that can be changed to correct this.
Any help would be appreciated. Ian Watson North Yorkshire UK

 

[Wolfgang Kais]

Hello ian.

I have an access database where I am looking to calculated
quantities that fall within particular date periods, grouping by
month is not a problem but when grouping by week (on the
report group function) some data does not appear in the same
week number as what should be on a calendar. If there any reason
why this happens and any settings that can be changed to correct
this.
Any help would be appreciated. Ian Watson North Yorkshire UK

Try to group by every value the following expression (instead of
grouping by week of yourDateField):
=DatePart("ww",[yourDateField],2,2)
I guess that the FirstDayOfWeek and FirstWeekOfYear parameters
of the DatePart function are the key to solve your problem.

 

[Ron2006]

When you are grouping by week number are you perhaps also selecting by
range of dates.

Remember that the same week can appear in two months if the month
breaks in the middle of the week.

If you are ranging by month and then summing by week, you will NOT get
all of the records that have that week number because they have a date
outside the range of dates.

Ron