{#post3800470} ++Introduction++

While I have been able to find an example of how to calculate the number of business days between two dates, I haven't been able to find a published method to add business days. Excel has recently added this function, but Microsoft has yet to give it to the Access world. So, I have undertaken this challenge. The function that I have created allows the user to specify what days are not included in the count (for weekends) and it also allows for a list of holidays to be excluded as well. Holidays must be entered in a table for this to work. I have created my own method for automatically getting this information from the Internet, but that is a subject for another post.

虽然我已经能够找到如何计算两个日期之间的工作日数的示例,但我找不到已发布的方法来添加工作日。 Excel最近添加了此功能,但微软尚未将其提供给Access世界。所以,我接受了这个挑战。我创建的功能允许用户指定计数中不包括的天数(周末),并且还允许排除假期列表。必须在表格中输入假期才能使用。我已经创建了自己的方法来自动从Internet获取此信息,但这是另一篇文章的主题。

++Tables++

The best method that I could come up with was to have a list of available dates and then to count down the records for the specified number of days. This means that we need a list of dates. Instead of having to type them all into a table, we will let Access take care of this problem. All we need is a table with a single field in it:

我能想到的最好的方法是获得可用日期列表,然后倒计数指定天数的记录。这意味着我们需要一个日期列表。我们不会将它们全部输入到表中,而是让Access解决这个问题。我们需要的只是一个包含单个字段的表:

  1. ++tbl_ExpansionDigits++
  2. Digit

Now add 10 records with the values 0 to 9

We also need a table of holidays. My table has a date field and a name field so that I can list the holiday name if I want to.

我们还需要一张假期表。我的表有一个日期字段和一个名称字段,以便我可以列出假日名称。

That is it for tables

++Queries++

We actually only need one query. This is what performs the magic of giving us dates. Mine is named qryPossibleDates.

我们实际上只需要一个查询。这就是为我们提供日期的魔力。我的名字叫做qryPossibleDates。

  1. SELECT Cdate([tbl_expansiondigits_1]![Digit]
  2. +[tbl_expansiondigits_2]![Digit]*10
  3. +[tbl_expansiondigits_3]![Digit]*100
  4. +[tbl_expansiondigits_4]![Digit]*1000
  5. +[tbl_expansiondigits_5]![Digit]*10000) AS CalcDate, Weekday(CalcDate) AS WeekDayNumber
  6. FROM tbl_expansiondigits AS tbl_expansiondigits_1, tbl_expansiondigits AS tbl_expansiondigits_2, tbl_expansiondigits AS tbl_expansiondigits_3, tbl_expansiondigits AS tbl_expansiondigits_4, tbl_expansiondigits AS tbl_expansiondigits_5
  7. WHERE ((([tbl_expansiondigits_1]![Digit]
  8. +[tbl_expansiondigits_2]![Digit]*10
  9. +[tbl_expansiondigits_3]![Digit]*100
  10. +[tbl_expansiondigits_4]![Digit]*1000
  11. +[tbl_expansiondigits_5]![Digit]*10000)>=36000));

This gives us dates from 7/24/1998 to 10/13/2173 (a total of 64,000 days) plus the weekday number (1 for Sunday, 7 for Saturday).

这给我们的日期从7/24/1998到10/13/2173(总共64,000天)加上工作日数字(星期日为1,星期六为7)。

++Code++

First we are going to need a function to convert a binary-like number to a decimal value. I use this to for binary flags to know which days to exclude from the count.

首先,我们需要一个函数将类二进制数转换为十进制值。我使用它来获取二进制标志,以了解从计数中排除的天数。

  1. Private Function ConvertToDecimal(lngBinary As Long) As Long
  2. Dim strNumber As String
  3. Dim i As Integer
  4. Dim lngAccumulator As Long
  5. Dim n As Integer
  6. Dim s As String
  7. Dim p As Integer
  8. strNumber = Format(lngBinary, "00000000")
  9. p = 1
  10. For i = 8 To 1 Step -1
  11. s = Mid(strNumber, p, 1)
  12. If s = "1" Then
  13. n = CInt(i) - 1
  14. lngAccumulator = lngAccumulator + 2 ^ n
  15. End If
  16. p = p + 1
  17. Next
  18. ConvertToDecimal = lngAccumulator
  19. End Function

Now for the main function.

  1. Public Function AddDays(dteStart As Date, intInterval As Integer, lngExcludePattern As Long) As Date
  2. Dim lngExcludeDates As Long
  3. Dim strSelect As String
  4. Dim strWhere As String
  5. Dim strSQL As String
  6. Dim db As DAO.Database
  7. Dim rst As DAO.Recordset
  8. Dim i As Integer
  9. If lngExcludePattern <> 0 Then
  10. lngExcludeDates = ConvertToDecimal(lngExcludePattern)
  11. strSelect = "SELECT CalcDate As AddedDate FROM qryPossibleDates"
  12. strWhere = "CalcDate = #" & dteStart & "# Or (CalcDate >= #" & dteStart & "# And CalcDate Not In(SELECT hDate FROM dbo_Holidays WHERE hDate >= #" & dteStart & "#)"
  13. If (lngExcludeDates And 64) > 0 Then
  14. strWhere = strWhere & " And WeekDayNumber <> 1"
  15. End If
  16. If (lngExcludeDates And 32) > 0 Then
  17. strWhere = strWhere & " And WeekDayNumber <> 2"
  18. End If
  19. If (lngExcludeDates And 16) > 0 Then
  20. strWhere = strWhere & " And WeekDayNumber <> 3"
  21. End If
  22. If (lngExcludeDates And 8) > 0 Then
  23. strWhere = strWhere & " And WeekDayNumber <> 4"
  24. End If
  25. If (lngExcludeDates And 4) > 0 Then
  26. strWhere = strWhere & " And WeekDayNumber <> 5"
  27. End If
  28. If (lngExcludeDates And 2) > 0 Then
  29. strWhere = strWhere & " And WeekDayNumber <> 6"
  30. End If
  31. If (lngExcludeDates And 1) > 0 Then
  32. strWhere = strWhere & " And WeekDayNumber <> 7"
  33. End If
  34. strWhere = strWhere & ")"
  35. strSQL = strSelect & " WHERE " & strWhere
  36. Debug.Print strSQL
  37. Set db = CurrentDb
  38. Set rst = db.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)
  39. rst.Move intInterval
  40. AddDays = rst!AddedDate
  41. Set db = Nothing
  42. rst.Close
  43. Set rst = Nothing
  44. Else
  45. AddDays = dteStart + intInterval
  46. End If
  47. End Function

First, I test for the value in lngExcludePattern. If it is 0, then I'm just counting calendar days and it skips to the bottom. Otherwise, I build a query string that pulls from the query above adding the criteria. You will notice that it starts with CalcDate = #" & dteStart & "#"

首先,我测试lngExcludePattern中的值。如果它是0,那么我只是计算日历天数,它会跳到底部。否则,我构建一个查询字符串,该字符串来自上面添加条件的查询。你会注意到它始于

. This is for situations where dteStart is not a day that is included in the query as it does mess with the days involved (I found this out after troubleshooting why my counts were off). It then has the criteria to only show records that are greater than or equal to dteStart and not in the list of holidays, again filtered to be greater than or equal to dteStart (for performance). I then test for the binary flags present to add additional criteria to exclude the days of the week that we don't want included. I then open the recordset and use the .Move method to go to the correct record in the recordset to get my date. And that is it!

。这适用于dteStart不是查询中包含的那一天的情况,因为它会影响所涉及的日期(我在排除了为什么我的计数关闭后发现了这一点)。然后它具有仅显示大于或等于dteStart且不在假日列表中的记录的标准,再次过滤为大于或等于dteStart(用于性能)。然后,我测试存在的二进制标志,以添加其他标准,以排除我们不想包含的一周中的日期。然后我打开记录集并使用.Move方法转到记录集中的正确记录以获取我的日期。就是这样!

++Use++

To use this function, you pass the start date to which you want to add x number of days (dteStart), the number of days that you want to add (intInterval) and then the days that you want to exclude (lngExcludePattern). The first two are pretty simple, but the Exclude Pattern needs more explanation. I have it setup to do it as binary flags. The first flag is Sunday and the last flag is Saturday. So if I want my weekends to be Saturday and Sunday, my Exclude Pattern would be 1000001. If I want Wednesdays excluded as well, then my pattern would be 1001001. If I just want Saturdays excluded it would be 0000001, which can also be entered as 1 because it is passed as a long integer. So if I want to add 5 days to 1/17/16, excluding Saturdays and Sundays, I would call my function like this:

要使用此功能,请传递要添加x天数的开始日期(dteStart),要添加的天数(intInterval),然后传递要排除的天数(lngExcludePattern)。前两个非常简单,但排除模式需要更多解释。我有它设置为二进制标志。第一个标志是星期日,最后一个标志是星期六。因此,如果我希望我的周末是星期六和星期日,我的排除模式将是1000001.如果我也希望星期三被排除,那么我的模式将是1001001.如果我只想要星期六排除它将是0000001,也可以输入为1,因为它作为一个长整数传递。所以,如果我想在2016年1月17日增加5天,不包括星期六和星期日,我会像这样调用我的函数:

  1. =AddDays(#1/17/16#, 5, 1000001)

++Conclusion++

There are probably other ways to do this, but this seemed like the most versatile method and it actually does what I want it to. If anyone has any critiques, I welcome them.

查看英文原文

查看更多文章

公众号:银河系1号

联系邮箱:public@space-explore.com

(未经同意,请勿转载)