Getting "Invalid use of property" trying to compute a date (1 Viewer)

[NebraskaUser]

Can someone tell me what's wrong with my function? I'm trying to compute the date last Sunday. Every time I run it I get "Invalid Use of Property". I've tried it with and without double quotes around the numbers following each Case statement.

Public Function DateLastSunday(DayOfWeek As Integer) As Date
Select Case DayOfWeek
Case "1"
Date
Case "2"
Date -1
Case "3"
Date -2
Case "4"
Date -3
Case "5"
Date -4
Case "6"
Date -5
End Select
End Function

 

[plog]

Functions return things.


As a side note, that Case isn't needed. You can logically achieve it in a 1 line expression.

 

[Gasman]

Firstly if it is an integer then you do not use quotes.?
Secondly you have to assign something, so it would be along the lines of

DateLastSunday = Date -DayOfWeek +1

No need for all the case statements I think, and if correct no need for the function for one line?

 

[CJ_London]

as plog says, one line

dateadd("d",-weekday(date())+1,date())

 

[NebraskaUser]

You're right. I don't need all those Case statements nor do I need the quotes. My first problem was I was missing "DateLastSunday" in front of the expression which followed each Case statement. That resolved my "Invalid Use of Property". Then I got output but not the correct output. When I typed
"?DateLastSunday(1)", I got 08/20/2018 - today's date - instead of 08/19/2018 the correct Sunday date. I had to remove the entire Select Case and type simply DateLastSunday = Date - Weekday. Now I'm getting 08/19/2018, the correct Sunday date. (I changed the argument name to Weekday.)

Also i used double quotes around the integer value because Adam Wilbert, the instructor, used them in his Select Case. I got the same result with and without the quotes.

I impressed with how quickly I got an answer from the forum. Thanks.

 

[plog]

I don't understand the broader context of how this thing is used. But I'd test all your possible Weekday values (especially Saturday).

To be honest, your function is either poorly named or called. It doesn't calculate last Sunday, it simply subtracts days from the current date. A function that truly generated the date of last sunday wouldn't require you to pass it anything. It would know today's date and be able to properly subtract the appropriate number of days to arrive at the prior Sunday's date.