Function to return the nearrest xDay

[Middlemarch]

This finds the nearest Saturday to dd as Long

Function NearestSaturday(dd As Long) As String
    Dim Sat As Long
    Select Case Weekday(dd)
        Case 1 To 3
            Sat = dd - Weekday(dd)
        Case 4 To 6
            Sat = dd + (7 - Weekday(dd))
        Case 7
        Sat = dd
    End Select
    NearestSaturday = format(CDate(Sat), "dddd,dd mmm yyyy")
End Function

Perhaps there's better methods but assuming that's ok I've been trying to modify it so it can return any Day.
For Sunday the 7 would change to 1 but I can't quite work out what the rest should be.

[madpiet]

I feel like the easiest way might be a calendar table. Find the top 7 days on or after today. Find the one that's Saturday. Something like this?

GETDATE() in Access = DATE()

SELECT TOP 1 TheDate, DayName, DayNumber
FROM CalendarTable
WHERE TheDate >= GETDATE()
AND DayName = 'Saturday'
ORDER BY TheDate DESC;

[Middlemarch]

Thanks, but I did want code I could also use in vb6, or Even Excel. Then a table gets complicated as you need to link to it etc.

[orange]

Here's a vba function that may serve your purpose.

' Purpose: To find the nearest Saturday to a supplied Date
' Procedure Kind: Function
' Procedure Access: Public
' Parameter inputDate (Date): Supplied Date
' Return Type: String
' Author: Jack
' Date: 02-Mar-25
' ----------------------------------------------------------------
Function NearestSaturday(inputDate As Date) As String
    Dim difference As Integer
    ' Calculate the difference in days from Saturday (7th day of the week)
    difference = 7 - Weekday(inputDate, vbSunday)
    ' If difference is greater than 3, subtract days to go back, otherwise add days to go forward
    If difference > 3 Then
        NearestSaturday = inputDate - (7 - difference)
    Else
        NearestSaturday = inputDate + difference
    End If
    ' Return the calculated Saturday in format "dddd, dd mmm yyyy"
    NearestSaturday = Format(CDate(NearestSaturday), "dddd, dd mmm yyyy")
End Function

Sample output:

?NearestSaturday(Date + 396)
Saturday, 04 Apr 2026

[Middlemarch]

Similar what I had. But your remarks If difference is greater than 3, subtract days to go back, otherwise add days to go forward
May help me alter it to handle any day.

[Middlemarch]

Why is it 3 for Saturday? Would this vary, or be the same for any day ?

[jojowhite]

you can also try:

Function NearestSaturday(d As Date) As Date
    Dim dayDiff As Integer
    dayDiff = Weekday(d, vbSunday)
    Select Case dayDiff
    Case Is = 1
        NearestSaturday = d
    Case Is = 7
        NearestSaturday = d - 1
    Case Is <= 3
        NearestSaturday = d - dayDiff
    Case Else
        NearestSaturday = d + (7 - dayDiff)
    End Select
End Function

[Middlemarch]

I'm really after the nearest day to any day except Sat (which I have).
e.g. for 13 Mar 2025 if Tuesday was wanted it'd be 11 Mar
I'm surprised it's so hard to work out, but I'm trying LOL. May even get there.

[jojowhite]

LOL. Mar 13 is a Thursday, so the nearest is Mar 15, Saturday.
and Mar 11 is a Tuesday.

the function i made is a "just in case" you need saturday also.

[Middlemarch]

>the function i made is a "just in case" you need saturday also.
I took it to return just Saturday. Is that wrong?
e,g.
Want the nearest Wed to any date
Want the nearest Fri to any date
etc.

And I should be able to work it out, but still struggling !

[jojowhite]

here is what ChatGPT has to say:

Function NearestDayOfWeek(dtm As Date, TargetDay As VbDayOfWeek) As Date
    Dim CurrentWeekday As VbDayOfWeek
    Dim ForwardDiff As Integer
    Dim BackwardDiff As Integer


    ' Get the weekday number of the given date
    CurrentWeekday = Weekday(dtm, vbSunday) ' Default start of the week is Sunday


    ' Calculate the forward and backward distance to the target day
    ForwardDiff = (TargetDay - CurrentWeekday + 7) Mod 7
    BackwardDiff = (CurrentWeekday - TargetDay + 7) Mod 7


    ' If both distances are equal, return the future date (or you can modify as needed)
    If ForwardDiff <= BackwardDiff Then
        NearestDayOfWeek = dtm + ForwardDiff
    Else
        NearestDayOfWeek = dtm - BackwardDiff
    End If
End Function

example:

MsgBox "Nearest Sunday: " & NearestDayOfWeek(date, vbSunday)

[Middlemarch]

ChatGPT? You can ask it stuff?
That's doing exactly what I was wanting. Thanks very much

[jojowhite]

sure you can ask.
start your question with:

"ms access vba nearest day of week for any date" (without quote)

[Middlemarch]

Magic! Will certainly try that out on my next problem.

[ArviLaanemets]

I'd go for (here I assume the week is US one, i.e. Sunday is 1st day of week) date of nearest Saturday with formula:

datSaturday = dd -(Weekday(dd)<5)*Weekday(dd) + (Weekday(dd)>4)*(7-Weekday(dd))

No need for any UDF's! In case OP wants the result as datestring for some reason, Format function may be added to formula too!