• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 289
  • Last Modified:

Access 2000 - Custom week calculation

Dear Experts,

I need to calculate weeks in Access from date following such logic

- if the date is smaller than today --> week0
- if the date is this week --> week0
- if the date is next week --> week1
- if the date is after next week --> week2

Basically the actual week is from monday to sunday, so as today is 2011-05-13 that is 6 by this formula - TodayWeekday: Weekday(Date()). This can have importance that the next week always starts at Monday so when the TodayWeekday is 2.

1 Solution
Patrick MatthewsCommented:
If you would want the week number to keep increasing as you extend out into the future:

SELECT Date() AS Today, SomeDate, 
    "week" & DateDiff("ww", Date(), IIf(SomeDate < Date(), Date(), SomeDate), 2) AS WeekNum
FROM SomeTable

Open in new window

csehzAuthor Commented:
This is perfect to me, thanks very much

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now