MS Access 2010(设计视图):返回当前星期的星期一，星期一作为星期的第一天 [英] MS Access 2010 (Design View): return Monday of the current week with Monday as 1st day of the week

问题描述

我需要使Access查询始终返回当前一周的星期一.我已经在Google/StackOverflow上看到了一些解决方案，但是它们是用SQL编写的，而且我是创建Access查询的初学者(我正在使用设计"视图进行创建).

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).

目标:星期应视为 M T W T F S S.然后，查询应始终返回当前星期的星期一.因此，如果是星期日，它仍应返回下一个星期一之前的星期一，否.谁能解释如何使用Access 2010中的设计视图"做到这一点?

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?

推荐答案

请记住，在这种情况下，我们正在处理日期，因此，如果执行Date() - 1，我们将在今天之前1天.

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()〜今天的日期

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.

值

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

所以我们有Date()-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.

今天的行为是2015年4月28日(星期二)

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

因此，从外面到里面；给我当前的工作日值. (1 =星期一，2 =星期二，依此类推)，然后从中减去1->这就是我们需要从当前日期减去多少天才能回到weekday值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).

这篇关于MS Access 2010(设计视图):返回当前星期的星期一，星期一作为星期的第一天的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！