Query Dilemma

[TheSearcher]

The user enters a date on a form: 2/18/2025
I need a query that will calculate the previous Monday and the coming Saturday. The result should look like this:

I'm familiar with DatePart and DateAdd but just can't figure out the logic. ANy help will be appreciated.

 

[DHookom]

If the date is a Monday, do you want the previous Monday to be the entered date or a week prior?
Using the Orders table in Northwinds, this query should provide the OrderDate and the previous Monday - Saturday

SELECT Orders.OrderDate,
DateAdd("d",-Weekday([OrderDate]-2),[OrderDate]) AS PreviousMonday,
DateAdd("d",-Weekday([OrderDate]-2),[OrderDate])+5 AS ComingSaturday
FROM Orders;

 

[TheSearcher]

If the date entered is a Monday I would want that date as the "Previous Monday".
I don't quite understand that query. There is a dash before Weekday and also a "-2". How could the -2 be hard coded?

 

[DHookom]

2/23/2025 isn't a Saturday. Did you try the query? It seemed to work for me with all the dates in the Northwind Orders. Open the immediate window and enter:
?DateAdd("d",-Weekday(#2/18/2025#-2),#2/18/2025#)
and
?DateAdd("d",-Weekday(#2/18/2025#-2),#2/18/2025#)+5

You should return the requested dates.

 

[TheSearcher]

My mistake. 2/22 is a Saturday.
The only reason those queries work is because -2 and +5 are hard coded.

 

[GPGeorge]

Well, yes, the queries work precisely because of the hard-coded offsets to the dates.

Traditionally, we count Sunday as the 1st day of the week in the US. I understand that differs elsewhere, but for the purposes of this discussion, we'll stick to the US Default.

So, if Sunday is day 1, Monday is day 2 -- hence the hard-coded offset to adjust the date in the expression.

And, of course, Friday is the fifth day after after Sunday -- hence the hard-coded offset to the the date.

So, yeah, to get other weeks days, you use different hard-coded offsets accordingly.

You can explore that yourself by adjusting the -2 and -5 to see which weekdays are returned.

 

[arnelgp]

you can create your own function:

' chatgpt
Function MondayOfADate(givenDate As Date) As Date
    Dim weekdayNum As Integer
    Dim dte As Date
    ' Get the weekday number (Sunday = 1, Monday = 2, ..., Saturday = 7)
    weekdayNum = Weekday(givenDate, vbSunday)
    
    ' Calculate Monday of the same week
    dte = DateAdd("d", 2 - Weekday(givenDate, vbSunday), givenDate)
    If Format$(dte, "ddd", vbSunday) = "Mon" Then
        dte = dte - 7
    End If
    MondayOfADate = dte
End Function

' chatgpt
Function SaturdayOfADate(givenDate As Date) As Date
    Dim weekdayNum As Integer
    Dim dte As Date
    ' Get the weekday number (Sunday = 1, Monday = 2, ..., Saturday = 7)
    weekdayNum = Weekday(givenDate, vbSunday)
    
    ' Calculate the coming Saturday
    dte = DateAdd("d", 7 - weekdayNum, givenDate)
    If Format$(dte, "ddd", vbSunday) = "Sat" Then
        dte = dte + 7
    End If
    SaturdayOfADate = dte
End Function

on your query:

Select 
          DateEnteredByUser, 
          MondayOfADate(DateEnteredByUser) As PrevMonday, 
          SaturdayOfADate(DateEnteredByUser) As NextSaturday 
From YourTableName;

 

[TheSearcher]

Thank you all for your responses.
GPGeorge - thanks for the explanation. It seemed to simple to be true.

 

[DHookom]

If I was going to create a solution for this, I would probably create a generic function that would accept a date, a time period like week or month, an offset of periods (positive or negative), and the day of week. The returned date would be a specific day of the week, x number of weeks or months based on the input date.