How to get the date of the next Sunday? 1

[MikeDamone]

I'm looking for a way to get the date of the next Sunday.

-If the date selected is Thursday, 7/1/2010, I need to know how to return 7/4/2010

-But if today is Sunday, 7/4/2010, I need to return that date. 7/4/2010

I'd like to do this in one query. Is this possible? I know how to do it in Oracle, but not that familiar with Access. Any help is greatly appreciated!

 

[traingamer]

iif(format([yourdate], "w") = 1, [yourdate], dateadd("d", 8 - format([yourdate], "w"), [yourdate]))

format([yourdate], "w") will return the day of the week 1= sunday, etc

So if it's already sunday, use the current date

otherwise

add the number of days needed to get the next sunday

dateadd("d", 8 - format([yourdate], "w"), [yourdate])

There are probably slightly simpler ways...

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[dhookom]

This expression seems to work:

 DateAdd("D",7-(Weekday([yourdate]-1)),[yourdate])

Duane
Hook'D on Access
MS Access MVP

 

[traingamer]

I knew someone would have a simpler answer.

Greg
People demand freedom of speech as a compensation for the freedom of thought which they seldom use. Kierkegaard

 

[MikeDamone]

Thanks everyone! I got it working with your suggestions