Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

Is there any way to list all the Sunday and Saturday for a given year (2013 for example) in Access ? I tried to search and see that VBA might do the trick. What I want is that I have a Form that ask for user to input [Start Date] and [End Date]. I want to calculate the [Working days] = [End Date] - [Start Date] - (any Sunday and Saturday in between if available). So I am thinking of listing all the Sunday and Saturday in that year first then somehow trying to calculate the [Working days]. Any ideas would be appreciate . Thank you. >.<

Ok I managed to come up with simple function that can track down the Saturday in a period of time. haha guess I can solve it myself

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
share|improve this question

1 Answer 1

If you search for work days in Access developer reference, it gives you code that will do that calculation for you

[Reproduced for posterity]

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.