# Access 2007 list all the Sunday and Saturday in a year?

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
``````
-

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)
EndDays = 0

Do While DateCnt <= EndDate
If Format(DateCnt, "ddd") <> "Sun" And Format(DateCnt, "ddd") <> "Sat" Then
EndDays = EndDays + 1
End If
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
``````
-