在Access中计算两个日期之间的工作日天数

虽然看起来是一个小问题，但很不幸的是在Access中没有直接的函数支持（Excel中有一个现成的函数：NETWeekdays)。所以非得自己做点开发

第一步：创建一个自定义的函数

Public Function WeekDayCount(firstDate As Date, LastDate As Date) As Integer
'计算工作日天数
    On Error GoTo Err:

    Dim i As Integer
    Dim TempDate As Date    '临时日期
    Dim Tempts As Long
    Tempts = DateDiff("d", firstDate, LastDate)
    For i = 0 To Tempts
        TempDate = DateAdd("d", i, firstDate)
        Select Case Format(TempDate, "w")
        Case 2, 3, 4, 5, 6
            WeekDayCount = WeekDayCount + 1
        End Select
    Next
Err:
    Exit Function

End Function

 

第二步：然后在查询中使用如下语句

SELECT WeekDayCount(开始日期,结束日期) AS 工作日天数, *
FROM orders;