Access计算两个日期之间的工作日天数的几个函数

时　间:2011-08-03 10:33:27
作　者:竹笛   ID：8  城市：上海  QQ：2851379730
摘　要:从The Access WEB网站（http://access.mvps.org/access）上发现的两个函数，给大家推荐一下。
正　文:

计算两个日期之间的工作日天数的几个函数

第一个函数：

'*********** Code Start **************

Public Function WorkingDays(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name:  WorkingDays
' Inputs:  StartDate As Date
'  EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date:  February 19, 1997
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function does not account for holidays.
'....................................................................
On Error GoTo Err_WorkingDays

Dim intCount As Integer

StartDate = StartDate + 1
'If you want to count the day of StartDate as the 1st day
'Comment out the line above

intCount = 0
Do While StartDate <= EndDate
'Make the above < and not <= to not count the EndDate

Select Case WeekDay(StartDate)
Case Is = 1, 7
intCount = intCount
Case Is = 2, 3, 4, 5, 6
intCount = intCount + 1
End Select
StartDate = StartDate + 1
Loop
WorkingDays = intCount

Exit_WorkingDays:
Exit Function

Err_WorkingDays:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays
End Select

End Function

第二个函数：

Public Function WorkingDays2(StartDate As Date, EndDate As Date) As Integer
'....................................................................
' Name:  WorkingDays2
' Inputs:  StartDate As Date
'  EndDate As Date
' Returns: Integer
' Author: Arvin Meyer
' Date:  May 5,2002
' Comment: Accepts two dates and returns the number of weekdays between them
' Note that this function has been modified to account for holidays. It requires a table
' named tblHolidays with a field named HolidayDate.
'....................................................................
On Error GoTo Err_WorkingDays2

Dim intCount As Integer
Dim rst As DAO.Recordset
Dim DB As DAO.Database

Set DB = CurrentDb
Set rst = DB.OpenRecordset("SELECT [HolidayDate] FROM tblHolidays", dbOpenSnapshot)

'StartDate = StartDate + 1
'To count StartDate as the 1st day comment out the line above

intCount = 0

Do While StartDate <= EndDate

rst.FindFirst "[HolidayDate] = #" & StartDate & "#"
If Weekday(StartDate) <> vbSunday And Weekday(StartDate) <> vbSaturday Then
If rst.NoMatch Then intCount = intCount + 1
End If

StartDate = StartDate + 1

Loop

WorkingDays2 = intCount

Exit_WorkingDays2:
Exit Function

Err_WorkingDays2:
Select Case Err

Case Else
MsgBox Err.Description
Resume Exit_WorkingDays2
End Select

End Function

'*********** Code End ************** 

图   示：

附  件:

点击下载此附件

Access软件网QQ交流群 (群号:54525238) 　　　　　　Access源码网店