Function to return the nearrest xDay

[Middlemarch]

That does Sat only, but do many places have Day1 as other than Sunday.
Can Access be aware of that ?
Added: Put Vba where Day1 as other than Sunday. in chatGPT and got a answer!

[ArviLaanemets]

That does Sat only, but do many places have Day1 as other than Sunday.
Can Access be aware of that ?

I assume this was about my post?

E.g. with European week definition, and wanting the nearest last day of week (Sunday) returned

datSunday = dd -(Weekday(dd;2)<4)*Weekday(dd;2) + (Weekday(dd;2)>3)*(7-Weekday(dd;2))

or wanting the nearest Saturday returned

datSaturday = dd -(Weekday(dd;2)<4)*Weekday(dd;2) + (Weekday(dd;2)>3)*(7-Weekday(dd;2)) -1

[Gustav]

You can add a final twist to the ChatGPT solution:

Public Function DateNearestWeekday( _
    ByVal BaseDate As Date, _
    ByVal DayOfWeek As VbDayOfWeek, _
    Optional ByVal Bias As Integer) _
    As Date
    
    Dim BaseWeekday As VbDayOfWeek
    Dim OffsetMajor As Integer
    Dim OffsetMinor As Integer
    Dim Offset      As Integer
    Dim WeekdayDate As Date


    ' The weekday of the base date.
    BaseWeekday = Weekday(BaseDate)


    ' Calculate the day count from the base date to the previous and the following weekday.
    OffsetMinor = (BaseWeekday - DayOfWeek + 7) Mod 7
    OffsetMajor = (DayOfWeek - BaseWeekday + 7) Mod 7
    
    If OffsetMajor = OffsetMinor Then
        Offset = Sgn(Bias) * 7
    ElseIf OffsetMajor < OffsetMinor Then
        Offset = OffsetMajor
    ElseIf OffsetMajor > OffsetMinor Then
        Offset = -OffsetMinor
    End If
    WeekdayDate = DateAdd("d", Offset, BaseDate)
    
    DateNearestWeekday = WeekdayDate
    
End Function

This will allow you to specify what to do, if the searched weekday is that of the date to search from:

' If the weekday is that of the base date, argument Bias may be specified:
'
'   Bias = 0: The base date (default)
'   Bias > 0: The next date of weekday
'   Bias < 0: The previous date of weekday