☰

Access 2007 列出一年中的所有星期日和星期六？

tag-icon • tag-icon vba microsoft-access-2007
Access 2007 列出一年中的所有星期日和星期六？

有没有办法在 Access 中列出给定年份（例如 2013 年）的所有星期日和星期六？我尝试搜索并发现 VBA 可能会解决问题。我想要的是，我有一个表单，要求用户输入 [开始日期] 和 [结束日期]。我想计算 [工作日] = [结束日期] - [开始日期] -（如果可用，则为中间的任何星期日和星期六）。所以我想先列出该年的所有星期日和星期六，然后以某种方式尝试计算 [工作日]。任何想法都将不胜感激。谢谢。>.<

好吧，我设法想出了一个简单的函数，可以追踪一段时间内的星期六。哈哈，我想我可以自己解决它

Private Sub Command38_Click()
    str = FindWeekend(#6/3/2013#, #6/17/2013#)
    MsgBox str
End Sub


Public Function FindWeekend(ByVal StartDate As Date, ByVal EndDate As Date) As String
    Dim Weekend() As Date
    Dim i, k As Integer

    i = 0

    Do Until StartDate = EndDate
        If Weekday(StartDate) = 7 Then
            ReDim Preserve Weekend(i)
            Weekend(i) = StartDate
            i = i + 1
        End If
        StartDate = StartDate + 1
        MsgBox StartDate
    Loop

    'Convert stored value to string to debug
    For k = 0 To UBound(Weekend)
        FindWeekend = FindWeekend & Format(Weekend(k), "dd-mm-yyyy") & ","
    Next k

End Function

答案1

如果你搜索工作日在 Access 开发人员参考中，它为您提供了可以为您执行该计算的代码

[为后人转载]

Function Work_Days(BegDate As Variant, EndDate As Variant) As Integer
   Dim WholeWeeks As Variant
   Dim DateCnt As Variant
   Dim EndDays As Integer

   On Error GoTo Err_Work_Days

   BegDate = DateValue(BegDate)
   EndDate = DateValue(EndDate)
   WholeWeeks = DateDiff("w", BegDate, EndDate)
   DateCnt = DateAdd("ww", WholeWeeks, BegDate)
   EndDays = 0

   Do While DateCnt <= EndDate
      If Format(DateCnt, "ddd") <> "Sun" And Format(DateCnt, "ddd") <> "Sat" Then
         EndDays = EndDays + 1
      End If
      DateCnt = DateAdd("d", 1, DateCnt)
   Loop

   Work_Days = WholeWeeks * 5 + EndDays

Exit Function

    Err_Work_Days:

    ' If either BegDate or EndDate is Null, return a zero
    ' to indicate that no workdays passed between the two dates.

    If Err.Number = 94 Then
        Work_Days = 0
        Exit Function
    Else
' If some other error occurs, provide a message.
    MsgBox "Error " & Err.Number & ": " & Err.Description
    End If

End Function

相关内容