Find the Nearest Previous Saturday

[RachelBedi]

Good Afternoon!

I need to write a query which pulls a date range, I don't want to write a between [enter start date] and [enter end date] - I want to write one query that doesn't need to be manipulated too much that will take a date range between [nearest previous saturday] and Date()

I hope that makes sense, if someone can give me a super simple solution that would be great :-D



Thank you!

[orange]

Here's a small procedure that may help.

Function NearestSaturday(Optional myDate As Date)
    Dim i As Integer
    If myDate = #12:00:00 AM# Then myDate = Date
    For i = 0 To 7
        If Weekday(myDate - i) = 7 Then
        Debug.Print "Requested Date is " & WeekdayName(Weekday(myDate)) & "  " & myDate
        Debug.Print "Previous " & WeekdayName(Weekday(myDate - i)) & " " & myDate - i
        End If
    Next i
End Function

You can submit a valid Date and the routine will tell you the Requested Day and Date and will tell you the Date of the previous Saturday.

Here is small test routine

Sub testNearestSaturday()
Debug.Print NearestSaturday(#4/23/1953#)
End Sub

and here is the result

Requested Date is Thursday 23/04/1953
Previous Saturday 18/04/1953

Hope it's useful to you