I have a shipping table that has ship_date, ship_code, and quantity columns (among others). I’m trying to determine the total quantity of products shipped each week during our production cycle, which runs from 12/01/2001 – 11/01/2002. In the query grid I set up three fields as follows:

1) A calculated field using the DatePart function:

Field: WEEK: DatePart(“ww”, [ship_date])
Table: blank
Total: Group By
Sort: Ascending
Criteria: blank

2) The quantity field

Field: quantity
Table: shipping
Total: Sum
Sort: blank
Criteria: blank

3) ship_date field

Field: ship_date
Table: shipping
Total: Where
Sort: blank
Criteria: Between #12/02/2001# And #11/01/2002#

When I run the query, it looks as if it works. I have one column with the ‘week’ number and a second column with product quantity totals. On closer look, I realize the total quantities are wrong. For example, when I run a separate query totaling products shipped Between 12/01/2001 And 12/07/2001, I get a different number from what appears next to week 1 in my other query.

Check out the arguments and defaults of using DatePart() with "ww".

By default, the DatePart() function starts each week on Sunday and ends it on Saturday, and week 1 of a year is the week in which Jan 1st falls, unless you change them in the function using the optional arguments.

So, by default, 12/01/2001 (Sat) - 12/07/2001 (Fri) falls partly on week 48 and partly on week 49 of the year 2001, not week 1 of year 2002 in the date range of #12/02/2001# - #11/01/2002#.


Note:
The first week and last week of a year may or may not have 7 days. For example, week 1 of year 2003 by default has only four days i.e. 1st - 4th January.

Thanks Jon. This helped.