CODEWITHSUNDEEP
vbams-access
Deke
Deke

Reputation: 495

MS Access VBA on Dateadd's for current day

I've got a question on the dateadd statement I'm using. The idea is that a form pops up with the day of the week and you can select it to add a due date. IE: You select Monday and if it is Monday then it will give you a due day of Monday but if it's Tuesday then the following Monday will be used for your due date.

The issue is that if it's Monday and you select Monday it is giving you the following Monday as a due date and not that current day.

Here is the statement I'm using...

 DateAdd('d',8-Weekday(Date(),4),Date())

I'm not sure how to make this correction since this seems correct to me. Any help would be greatly appreciated.

Upvotes: 0

Views: 186

Answers (3)

Vityata
Vityata

Reputation: 43585

If you do not need to use a 1 line solution, then a small function would be quite handy. It checks the current day with Weekday() and if it is vbMonday it returns it. If not, it gives the next Monday.

Public Function NextMonday(selectedDate As Date) As Date

    Dim returnMonday As Date

    If Weekday(selectedDate) = vbMonday Then
        NextMonday = selectedDate
    Else
        NextMonday = selectedDate + 8 - Weekday(selectedDate, vbMonday)
    End If

End Function

And it works:

Sub TestMe()

    'Monday:
    Debug.Print NextMonday(DateSerial(2019, 6, 10))
    Debug.Print NextMonday(DateSerial(2019, 6, 17))

    'Not Monday:
    Debug.Print NextMonday(DateSerial(2019, 6, 7))
    Debug.Print NextMonday(DateSerial(2019, 6, 11))

End Sub

Upvotes: 0

Alvaro CC
Alvaro CC

Reputation: 142

This should also work:

DateAdd('d',(8-Weekday(Date(),4)) mod 7,Date())

Upvotes: 1

Deepstop
Deepstop

Reputation: 3807

This seems to work.

thisday = date()

dateadd("d",7-weekday(thisday, vbTuesday), thisday)

Upvotes: 1

Related Questions