get date of previous Thursday 2

[THWatson]

Using Access 2000

I want to be able to set the previous Thursday's date in a text box.

If the date is today (February 6), the following SQL will give me the correct date of the previous Thursday, which in this case is February 5

SELECT Date() AS MyDate, [MyDate]-Weekday([MyDate])+IIf(Weekday([MyDate])<=5,0,7)-2 AS Expr2;

Will this be correct in all circumstances?

Thanks.

Tom

 

[pbaldy]

A brief test showed it to work, though it subtracted a week when the date was a Thursday. In other words, a date of 2/5 returns 1/29 rather than 2/5. Not sure if that's what you want. I would have done this, which is what I tested against:

[MyDate]-Choose(Weekday([MyDate],1),3,4,5,6,0,1,2)

If you wanted the previous week on Thursday, all you'd need to do is change the 0 to a 7.


Paul
MS Access MVP 2007/2008

 

[PHV]

I'd use this:
SELECT Date()-Weekday(Date())+IIf(Weekday(Date())<=5,0,7)-2 AS PrevThursday

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

 

[THWatson]

Paul
You discerned correctly. When it's already a Thursday, I want to stay on that date.

PHV
I'm trying to figure out what the difference is between what I posted, given that MyDate = Date

SELECT Date() AS MyDate, [MyDate]-Weekday([MyDate])+IIf(Weekday([MyDate])<=5,0,7)-2 AS Expr2;

and what you would use

SELECT Date()-Weekday(Date())+IIf(Weekday(Date())<=5,0,7)-2 AS PrevThursday

Tom

 

[softhemc]

As an alternative (which makes it easy to change the day of the week) ...

SELECT Date() AS MyDate, 5 AS DOW, DateAdd("d",MyDate-DateDiff("d",DOW,MyDate) Mod 7,0) AS RequiredDate FROM Table1;

 

[THWatson]

softhemc

Good alternative.

Thanks.

Tom