ms-access - 将 DatePart() 与跨越日期部分边界的日期范围结合使用

Question

我目前正在尝试将一些数据表汇总成报告。表中的每条记录都包含一个日期范围，如下所示:

StartDate    EndDate
--------------------
13/04/13     15/04/13
17/04/13     24/04/13
28/04/13     03/05/13
05/05/13     10/05/13

假设日期范围表示休假天数之类的内容，我希望能够计算出每月休假的总天数。我遇到了 DatePart 函数，它似乎可以在一个边缘情况下工作:当日期范围跨越月份边界时。由于 DatePart 函数返回一个给定日期的月份，因此我无法再使用它来确定该边缘情况记录的休假天数(在上面的示例中是记录 3)，因为它适用于两个单独的日期月。

理想情况下，我希望我的决赛 table 看起来像:

 Month       #OfDays
--------------------
   4           11               (1st record - 2, 2nd record - 7, 3rd record - 2)
   5           8                (3rd record - 3, 4th record - 5)

我考虑过一些困惑的选项，例如填充一个临时表，其中每条记录都表示不同的一天，然后对其进行查询，但我不确定这如何与报告联系起来。现在我的报告记录源是(不正确的)查询，是否可以将记录源作为返回记录源的 VBA 函数？

我认为的另一件事是可能有一个初始查询，将任何边缘情况分成两个单独的记录，其中日期范围仅涵盖一个月，然后将其用于我的最终分组查询。这可能吗？

我觉得这个问题可能有一个更简单的解决方案，但我看不到。

如果有人有任何想法，我们将不胜感激!

Answer 1

要使用 Access 查询完成任务，您需要创建一个名为 [Numbers] 的表，其中包含一个名为 [n] 的数字(长整数)列，其中包含数字 1、2、3...，直到最高值您期望合作的年份。我创建了我的如下

   n
----
   1
   2
   3
 ...
2499
2500

您还需要将以下 VBA 函数粘贴到 Access 模块中

Public Function IsValidDayOfYear(YearValue As Long, DayValue As Long) As Boolean
Dim IsLeapYear As Boolean
If (YearValue Mod 400) = 0 Then
    IsLeapYear = True
ElseIf (YearValue Mod 100) = 0 Then
    IsLeapYear = False
ElseIf (YearValue Mod 4) = 0 Then
    IsLeapYear = True
Else
    IsLeapYear = False
End If
IsValidDayOfYear = (DayValue <= IIf(IsLeapYear, 366, 365))
End Function

假设您的源表名为 [DateRanges]。我们首先创建一个查询，为源表中表示的每年生成一年中的每一天。这里的技巧是 DateSerial() “滚动”月份边界，所以

日期序列(2013, 1, 32) = #2013-02-01#

和

日期序列(2013, 1, 234) = #2013-08-22#

SELECT DateSerial(yr.n, 1, dy.n) AS [Date]
FROM Numbers yr, Numbers dy
WHERE 
    (
        yr.n 
            BETWEEN (SELECT MIN(DatePart("yyyy", DateRanges.StartDate)) FROM DateRanges)
                AND (SELECT MAX(DatePart("yyyy", DateRanges.EndDate)) FROM DateRanges)
    )
    AND (dy.n < 367) AND IsValidDayOfYear(yr.n, dy.n)

对于您的示例数据，该查询返回 2013 年的所有日期。

让我们将该查询保存为 [AllDays]。现在我们可以使用它来提取每个日期范围的各个日期(省略 StartDate，以便最终计数与问题中的计数相匹配)

SELECT [Date] FROM AllDays
WHERE EXISTS 
    (
        SELECT * FROM DateRanges 
        WHERE AllDays.[Date] BETWEEN DateAdd("d", 1, DateRanges.StartDate) AND DateRanges.EndDate
    )

这将返回与每个范围相对应的各个日期，即

Date      
----------
2013-04-14
2013-04-15
2013-04-18
2013-04-19
2013-04-20
2013-04-21
2013-04-22
2013-04-23
2013-04-24
2013-04-29
2013-04-30
2013-05-01
2013-05-02
2013-05-03
2013-05-06
2013-05-07
2013-05-08
2013-05-09
2013-05-10

我们可以将该查询保存为 [RangeDays]，然后用它来按月计算计数...

SELECT DatePart("m", [Date]) AS [Month], COUNT(*) AS NumOfDays
FROM RangeDays
GROUP BY DatePart("m", [Date])

...返回

Month  NumOfDays
-----  ---------
    4         11
    5          8