查询“工作周"；在Access SQL中 [英] querying for "workweek" in Access SQL

问题描述

我有一个来自MS Excel 2007的公式，该公式返回某个月内某个日期的工作周.

I have a formula from MS Excel 2007 which returns the workweek of a certain date within a certain month.

B1 = IF(AND(WEEKDAY(A2,1)<>1,A2=DATE(YEAR(A2),MONTH(A2),1)),A2-WEEKDAY(A2,1)+7,IF(MONTH(A2)<>MONTH(A2-WEEKDAY(A2,1)+7),EOMONTH(A2,0),A2-WEEKDAY(A2,1)+7))

其中A1 = 11/2012和B1 = 1/7/2012

where A1 = 11/2012 and B1 = 1/7/2012

我想在MS Access中使用此公式，并将返回相同的值.我曾尝试提出查询，但无法正常工作.

I'd like to use this formula in MS Access and will return the same value. I'd tried to formulate a query but it won't work.

列A1的字段名称为[rdate]，列B1的字段名称为[we_date].

The field name of column A1 is [rdate] and column B1's field name is [we_date].

推荐答案

不计算，查询.参见:

为什么我应该考虑使用辅助日历表?

日历表可以发挥很多作用 更容易开发任何解决方案 涉及日期的商业模式. 最后我检查了一下 几乎所有您可以使用的商业模式 想到了，在某种程度上.持续的 最终需要解决的问题 冗长，复杂和低效 方法包括以下 问题:

A calendar table can make it much easier to develop solutions around any business model which involves dates. Last I checked, this encompasses pretty much any business model you can think of, to some degree. Constant problems that end up requiring verbose, complicated and inefficient methods include the following questions:

• x和y之间有多少个工作日?
• 3月的第二个星期二至4月的第一个星期五之间的所有日期是什么?
• 我希望这批货物什么时候到达?
• 本季度所有星期五的日期是什么?
• ...

• How many business days between x and y?
• What are all of the dates between the second Tuesday of March and the first Friday in April? 
• On what date should I expect this shipment to arrive? 
• What were the dates of all the Fridays in this quarter? 
• ...  

这篇关于查询“工作周"；在Access SQL中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！