Access VBA - date of previous monday or tuesday etc

[RiddleMeThis]

Im trying to get the date of the previous day as specified at code level (i have a form for each day of the week so it will change from form to form).

Basically I want a variable containing the date of the previous monday for example (but could be any day), including today, so if this is monday i want today's date not last monday's, if this is friday then i want last monday's date.

At the minute im using the following VBA code with errors:


Dim strdate As String

Select Case WeekdayName(Now())
Case vbMonday
Set strdate = Now()
Case vbTuesday
Set strdate = DateAdd("d", -2, Now())
Case vbWednesday
Set strdate = DateAdd("d", -3, Now())
Case vbThursday
Set strdate = DateAdd("d", -4, Now())
Case vbFriday
Set strdate = DateAdd("d", -5, Now())
Case vbSaturday
Set strdate = DateAdd("d", -6, Now())
Case vbSunday
Set strdate = DateAdd("d", -7, Now())
End Select

MsgBox "Monday = " & strdate, vbOKOnly, "Monday"


The error i get is:
Compile error
Object Required
and it points to strdate in the first case.

Any ideas? If i havent explained it well let me know

 

[PHV]

Dim strdate As String
Select Case WeekdayName(Now())
Case vbMonday
  strdate = Now()
Case vbTuesday
  strdate = Now() - 1
Case vbWednesday
  strdate = Now() - 2
Case vbThursday
  strdate = Now() - 3
Case vbFriday
  strdate = Now() - 4
Case vbSaturday
  strdate = Now() - 5
Case vbSunday
  strdate = Now() - 6
End Select
MsgBox "Monday = " & strdate, vbOKOnly, "Monday"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Remou]

Hi PHV

vbMonday etc are numeric values. Weekday accepts date, WeekdayName accepts integer.

http://lessthandot.com

 

[dhookom]

I'm not sure why if you want to "get the date", you are returning a string.

I would create a generic function the returns the date of the previous or next day of the week with the option to set a base date:

Function GetPrevNextDate(intDOW As Integer, strPN As String, Optional datBase As Date) As Date
[green]    'intDOW = 1 - 7 for Sunday - Saturday
    'strPN = "P" for Previous or "N" for Next
    'if the current day of week = intDOW, _
        the datBase date will be returned [/green]
    Dim intStep As Integer
    If datBase = 0 Then
        datBase = Date
    End If
    If strPN = "P" Then
        intStep = -1
     Else
        intStep = 1
    End If
    Do Until Weekday(datBase) = intDOW
        datBase = datBase + intStep
    Loop
    GetPrevNextDate = datBase
End Function

Duane
Hook'D on Access
MS Access MVP

 

[Golom]

You are getting "Object Required" because "Set" is used to assign values to objects but "strDate" is a string ... not an object. Just drop the "Set".

You can reduce this to

strDate = Format(Dateadd("d", vbSunday-WeekDay(Date) ,Date) + 1, "yyyy-mm-dd")

 

[PHV]

OOps, thanks Remou.
Select Case Weekday(Now())

 

[RiddleMeThis]

Thanks to everyone who replied, really appreciate it. I tried and tested each method to the best of my understanding, but in the end, went with PHVs way of doing it with input from remou. tbh i didnt really understand dhookom's way, and golum, your way is a really clean cut way of doing it, i really liked it, but it progresses onto the next few days instead of only going back into the previous week. I know this could be sorted with a case statement or something, but PHVs was the closest way to how i had done it myself, so i understood it and felt more comfortable with how it works.

Thanks again to everyone :]

[COLOR== green]
Dim strDate As String

Select Case Weekday(Now())
Case vbMonday
strDate = Now()
Case vbTuesday
strDate = Now() - 1
Case vbWednesday
strDate = Now() - 2
Case vbThursday
strDate = Now() - 3
Case vbFriday
strDate = Now() - 4
Case vbSaturday
strDate = Now() - 5
Case vbSunday
strDate = Now() - 6
End Select

MsgBox "Monday = " & strDate, vbOKOnly, "Monday"
[/color]

 

[dhookom]

My suggestion was to create the function and save it in a module named "modDateCalcs". Then you could use it anywhere in your other code, queries, control sources, etc. It would allow you to look for previous or next from any date with a default of today. Any expressions/code that use Now() rather than Date() will also include the time of the day which I don't think you want/need.

Then, if you want to MsgBox the previous Monday, the code would be:

    MsgBox "Monday = " & GetPrevNextDate(2,"P"), vbOKOnly, "Monday"

Duane
Hook'D on Access
MS Access MVP

 

[RiddleMeThis]

You're right i didnt want the time, i never realised this :] Thanks for the followup I get it better now, will look into ammending things as soon as I have time.
Cheers :]