While preparing Access Expert Level 27, working with the DatePart function, I discovered a bug in one of the formula's on Microsoft's web site. Here's the email I sent to the Access MVP mailing list about it. I'll share any relevant replies.
Hey guys. I think I discovered a bug in one of the formulas listed on Microsoft's site. I could use your help to verify my findings. I spent about an hour Googling (yes, and Binging) this bug to see if anyone else had reported it, but I found no mention of it anywhere, so I figured I'd share it here with you.
I'm putting together a tutorial on using Microsoft Access date functions. While researching uses for the DatePart function, I discovered these two formulas on Microsoft's web site that don't always work. They're formulas for determining if a date falls within the current week, previous week or the next week. They can be found on THIS page.
Contain dates within the previous week
Year([SalesDate])* 53 + DatePart("ww", [SalesDate]) = Year(Date())* 53 + DatePart("ww", Date()) - 1
Contain dates within the following week
Year([SalesDate])* 53+DatePart("ww", [SalesDate]) = Year(Date())* 53+DatePart("ww", Date()) + 1
For most purposes, these formulas work just fine, however, because the DatePart function resets to 1 on the first of the year, you're left with week values that look like this:
Date DatePart("ww",D)
12/29/2014 53
12/30/2014 53
1/1/2015 1
1/2/2015 1
1/3/2015 1
1/4/2015 2
1/5/2015 2
1/6/2015 2
Thus, if you plug these dates into the above formulas, a date on 12/29/2014 does NOT appear to be in the week previous to 1/4/2015, which is incorrect.
I tried modifying the formulas as follows:
Date in next week:
Year(D) * 53 + DatePart("ww",D,1,3) = Year(Date()) * 53 + DatePart("ww",Now(),1,3) + 1
Date in previous week:
Year(D) * 53 + DatePart("ww",D,1,3) = Year(Date()) * 53 + DatePart("ww",Now(),1,3) - 1
These functions use the optional fourth parameter which forces DatePart to only count WHOLE WEEKS of the year. So week 1 will actually start on 1/4/15. You get dates like this:
Date DatePart("ww",D,1,3)
12/29/2014 52
12/30/2014 52
1/1/2015 52
1/2/2015 52
1/3/2015 52
1/4/2015 1
1/5/2015 1
1/6/2015 1
You will run into years with 53 weeks, but those are handled correctly:
Date DatePart("ww",D,1,3)
12/30/2017 52
12/31/2017 53
1/1/2018 53
1/2/2018 53
1/3/2018 53
1/7/2018 1
However the math STILL doesn't work out right because if today is 12/30/14, then you get (106742 + 53) = 106795 for the one side of the equation, yet a date in NEXT WEEK such as 1/4/15 yields (106795 + 1) = 106796 for the other side because there is no 53rd week this year. The equations don't work.
I tried switching the function to:
Year(D) * 52 + DatePart("ww",D,1,3) = Year(Date()) * 52 + DatePart("ww",Now(),1,3) + 1
And this works MORE often than the other one, however it still breaks down in years with 53 weeks, like 2017.
The solution I ended up with doesn't use DatePart and this mathematical approach. I ended up using the WeekDay function to determine the first day of the week (Sunday) and calculating whether or not the date in question falls within 7 days of that. This gives the correct value for everything I've tried so far:
Date in the current week of the year
D Between (Date()-WeekDay(Date())+1) and (Date()-WeekDay(Date())+7)
Date in the following week of the year
D Between (Date()-WeekDay(Date())+8) and (Date()-WeekDay(Date())+14)
Date in the previous week of the year
D Between (Date()-WeekDay(Date())) and (Date()-WeekDay(Date())-6)
This appears to yield the correct results (today's date is 12/30/2014):
D Prev This Next
12/20/2014 False False False
12/21/2014 True False False
12/24/2014 True False False
12/25/2014 True False False
12/26/2014 True False False
12/27/2014 True False False
12/28/2014 False True False
12/29/2014 False True False
12/30/2014 False True False
1/1/2015 False True False
1/2/2015 False True False
1/3/2015 False True False
1/4/2015 False False True
1/5/2015 False False True
1/6/2015 False False True
1/10/2015 False False True
1/11/2015 False False False
12/28/2015 False False False
I have to say that I discovered this bug quite by accident. If I didn't just so happen to be working at the end of a year, where the NEXT WEEK falls in the following year AND this happened to be a year with 53 weeks, I would have never caught this. :)
I would certainly welcome your input on this matter, and if anyone here is in touch with the folks at Microsoft, you should let them know that they should remove the above formula from their site. It works most of the time, but as you can see, it's not perfect.
Thanks.
UPDATES:
Microsoft is apparently aware of the bug:
10 years ago
