VBA To Return the Date of Next Sunday, then the following Sunday, and so on 1

[MattGreer]

I thought this would be simple but it's confounding me so I find myself coming here to ask you geniuses how to do this.

Using MS Access 365, I'm trying to have VBA tell me the date of next Sunday. I've been using this:

Format(Now() + 9 - Weekday(Now() + 1, vbSunday), "yyyy-mm-dd")

But it breaks down if I run it on Saturday, picking the *following* Sunday rather than tomorrow.

Once that's fixed, I would like to be able to have the code tell me the date of each successive Sunday based on what the user enters. So let's say I'm running the code on 2021-05-22, Saturday. If I enter a '1', it gives me 2021-05-23. If I enter a '2', it'll tell me 2021-05-30. If I enter a '3', it'll tell me 2021-06-06, and so on.

Thanks!!


Matt

 

[combo]

Refer rather to Saturday:
[tt]Format(Now() + 1 + 7*n - Weekday(Now(), vbSaturday), "yyyy-mm-dd")[/tt]
For Sunday, if n=1, it returns next Sunday.

combo

 

[MattGreer]

SNEAKY

I checked like, 30 minutes ago so I went back and tried to figure it out and what I came up with was to figure out the date of LAST Sunday, and then ask the user (me) how many weeks in advance we're looking. Then just add that many weeks to last Sunday's date. Needless to say, your code is much, much cleaner.

Thank you!



Thanks!!


Matt

 

[mintjulep]

See this Excel FAQ

javascriptpenindex(450,450,'

http://www.tipmaster.com/includes/refinfo.cfm?w=450&h=450')

OK, copying Thread and FAQ links fails on mobile.

Here. faq68-6900

 

[MattGreer]

Combo,

For some reason the code isn't working now. It was working earlier. Went and had dinner, and now it's off by one day.

Here's what I have, and the resulting output

iWeekSel = InputBox("How many Sundays in advance?" & vbCrLf & vbCrLf & "Next Sunday = 1, week after next = 2, etc.", "Date Selection", "1")
stDate = Format(Now() + 1 + 7 * iWeekSel - Weekday(Now(), vbSaturday), "yyyy-mm-dd")
Debug.Print "Now: " & Now()
Debug.Print "Selected Week: " & iWeekSel
Debug.Print Now() + 1 + 7 * iWeekSel
Debug.Print Weekday(Now(), vbSaturday)
Debug.Print stDate

The output is this:

[tt]Now: 5/22/2021 7:17:37 PM
Selected Week: 1
5/30/2021 7:17:38 PM
1
2021-05-29

[/tt]

I have no idea why it would change over the past two hours. Scratching my head as it worked perfectly before. I tried putting parens around the 7 * n but didn't make a difference. Thoughts?

Thanks!!


Matt

 

[macropod]

Try:

MsgBox Format(Int((Now) / 7) * 7 + 1 + (InputBox("How many Sundays in advance?") - 1) * 7, "YYYY MM DD")

An offset of :
-1 will give the Sunday before last
0 will give last Sunday
1 will give next Sunday
2 will give the Sunday after next
and so on.

Cheers
Paul Edstein
[Fmr MS MVP - Word]

 

[combo]

Sorry, Weekday should be Sunday based:
[tt]stDate = Format(Now() + 1 + 7 * iWeekSel - Weekday(Now(), vbSunday), "yyyy-mm-dd")[/tt]
In this case Sunday =1, 1-1=0, and the date shifts to another Sunday. For other weekdays, 1-Weekday(Now(), vbSunday) returns -1 for Monday, -2 for Tuesday etc., always adjusting date to Sunday.


combo

 

[MattGreer]

Following up: works like a champ. Thank you again!

Thanks!!


Matt