Complex Report Expression-MS Access 2016: Return previous week of current week (Monday - Sunday) and same for previous year? (1 Viewer)

[demdej]

I need to compare data from the previous week of the current week (with each week starting on Monday) to the same week of the previous year.

This is the expression I have for the first day of the week for the previous week is : =Date()-(6+Weekday(Date(),2))

for the last day of the previous week is: =Date()-Weekday(Date(),2)

I know that I can get the previous year with this function: =DateAdd("yyyy",-1,Date()-Weekday(Date(),2))

This is what I use to get data that occurred in the previous week: =Count(IIf((Date()-(6+Weekday(Date(),2))) Between (Date()-(6+Weekday(Date(),2))) And (Date()-Weekday(Date(),2)),1,0))

I attempted to use the same function for the previous year by adding =DateAdd("yyyy",-1,Date()) to each 2022 date, but when I run my report, I'm still seeing data calculated from 2022 and not 2021. How can I return the counts for the same week in the previous year?

This is what I tried last: =Sum(IIf(DateAdd("yyyy",-1,Date()-(6+Weekday(Date(),2))) BETWEEN DateAdd("yyyy",-1,Date()-(6+Weekday(Date(),2))) AND DateAdd("yyyy",-1,Date()-Weekday(Date(),2)),1,0))

How would this affect the leap year? (cross posted)

 

[plog]

7 doesn't go into 365 nor 366 evenly. Every year spans at least 53 weeks (some span 54). There is never a natural 'same' week of the previous year. You must define it, and it will always be wonky.

I suggest you grab a 2023 calendar and pick 4 dates and manually define what all the ranges you want for them. One date should be in the first 3 days of January, one date should be within the first 3 days of march, then one random one between April and November and then one within the last 3 days of December.

Then for each of those dates define the start and end dates of every week (current week/year, prior week/current year, curent week/ prior year, prior week/prior week) you want.