I'm using an Access query to capture the quantity of specific items ordered by week in Microsoft Access. My dataset runs from June 1, 2018 to May 31, 2019. I have added a column to pull the week number from each corresponding order date, and it works fine as long as I start with the first week of a calendar year (first week of January) using the following expression in the query field:
Week No: DatePart("ww",[date],1,1)
My problem is that I want the week of June 1, 2018 to show as the first week of the year and continue chronologically (with week 53 corresponding to the last week in May 2019). June 1, 2018 falls within week 22 of the calendar year, so I modified the expression to this:
Week No: DatePart("ww",[date],1,22)
However, I now receive a "#func" error in the field for all rows.
Is there a way to edit this expression so the "year" I'm looking at follows the pattern of June 1 - May 31 and the week numbers reflect this?