0

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?

1

The final firstweekofyear argument for the DatePart function will only accept one of four enumerations:

  • vbUseSystem (0) Use the NLS API setting.
  • vbFirstJan1 (1) Start with week in which January 1 occurs (default).
  • vbFirstFourDays (2) Start with the first week that has at least four days in the new year.
  • vbFirstFullWeek (3) Start with first full week of the year.

As such, supplying this argument with a value of 22 will result in an error.


Since the year defined as June 1, 2018 to May 31, 2019 is offset from the calendar year by 151 days, you'll need to offset your calculations by this amount.

Therefore, the calculation becomes:

Week No: datepart("ww",datevalue([date])-151)

For example:

?datepart("ww",#2018-06-01#-151)
 1 
?datepart("ww",#2018-07-01#-151)
 5 
?datepart("ww",#2019-04-01#-151)
 44 
?datepart("ww",#2019-05-31#-151)
 53 

To future-proof the expression for future years, you may also want to calculate the offset on the fly (since it may not always be 151 given that some years will be leap years).

To achieve this, you might use the following expression:

Week No: datepart("ww",datevalue([date])-(dateserial(year(date())-iif(month(date())<6,1,0),6,1)-dateserial(year(date())-iif(month(date())<6,1,0),1,1)))
  • Thank you for the reply and thorough explanation. I understand the logic behind this; however, when I enter Week No: datepart("ww",[date]-151) I now receive "#Error" in all rows. Any thoughts why this is? Thanks again! – Rash8151 Jun 27 '19 at 23:46
  • Is [date] a valid date field in your database? – Lee Mac Jun 28 '19 at 11:57
  • Yes, it is. I tested to be sure it would still work with original expression, and it does. – Rash8151 Jun 28 '19 at 13:36
  • @erlynn86 I cannot reproduce this behaviour. If I create a new table with a field called date of Date/Time data type, the above expressions return the appropriate week numbers in a query. – Lee Mac Jun 28 '19 at 17:28
  • Thank you, Lee. Could my issue be that my date field includes date AND time (this is part of the original text data that I was provided)? I changed the field to only display mm/dd/yyyy; however, I noticed that the time is still being captured. – Rash8151 Jul 1 '19 at 11:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.