MS Access 2010 (Design View): return Monday of the current week with Monday as 1st day of the week

Question

I need to make my Access query always return the Monday of the current week. I have seen a few solutions on Google/StackOverflow but they are written in SQL and I am a beginner in creating Access queries (I am using the Design view to make them).

Goal: The week should be considered as M T W T F S S. Then, the query should always return the Monday of the current week. Therefore, if it is Sunday, it should still return the Monday before, NOT the next week's Monday. Can anyone explain how to do this using the Design View in Access 2010?

Does this work for you? `Date() - (DatePart("w", Date(), 2, 1)-1)` .. You can paste this into the `Field` column in Query Design View. If it works for you I can provide explanation. — Mark C., Apr 27 '15 at 15:04
@Invent-Animate it worked! I'd love to know why. Go ahead and write it as an answer so I can give it a green checkmark! — whatwhatwhat, Apr 27 '15 at 15:24
here something to read about datepart: http://www.techonthenet.com/excel/formulas/datepart.php — Krish, Apr 27 '15 at 15:28

score 9 · Accepted Answer · answered Apr 27 '15 at 16:51

Keep in mind that in this context we are working with dates, so if we do Date() - 1, we will get 1 day prior to today.

Date() ~ Today's date

DatePart(
        "w" - Weekday
        Date() - Today's date
        2 - vBMonday (Access assumes Sunday is the first day of the week, which is why this is necessary.)
        1 - vbFirstJan1 - This gets into using the first week of the year. We could have omitted this, as 1 is the default.
)

-1 - Subtract 1 from the DatePart value.

Values

Date() = 4/27/2015 (at time of this writing)
DatePart("w",Date(),2,1) = 1
DatePart("w",Date(),2,1)-1 = 0

So we have Date()-0... Okay, what's so great about that? Well, let's look at a more useful scenario where today's date is a day other than Monday.

Let's act like today is 4/28/2015 (Tuesday)

Date() = 4/28/2015
DatePart("w",Date(),2,1) = 2
DatePart("w",Date(),2,1)-1 = 1

So, from the outside, in; give me the current weekday value. (1 = Monday, 2 = Tuesday, etc.), and subtract 1 from that -> that's how many days we need to subtract from the current date to get back to the weekday value of 1 (Monday).

Wow, it makes so much sense now!! Thanks for your time!! – whatwhatwhat Apr 27 '15 at 17:32 — whatwhatwhat, Apr 27 '15 at 17:32

score 1 · Answer 2 · answered Apr 27 '15 at 16:53

Here's a function that will do this:

Public Function DatePrevWeekday( _
  ByVal datDate As Date, _
  Optional ByVal bytWeekday As VbDayOfWeek = vbMonday) _
  As Date

' Returns the date of the previous weekday, as spelled in vbXxxxday, prior to datDate.
' 2000-09-06. Cactus Data ApS.

  ' No special error handling.
  On Error Resume Next

  DatePrevWeekday = DateAdd("d", 1 - Weekday(datDate, bytWeekday), datDate)

End Function

As vbMonday is 2 and your date is today, you can use the core expression in a query:

PreviousMonday: DateAdd("d",1-Weekday(Date(),2),Date())

MS Access 2010 (Design View): return Monday of the current week with Monday as 1st day of the week

2 Answers2

Values

Linked