Calculate Day/Date Help

[Sharkbyte]

I would like to request some help in developing the correct code to determine
a day/date.

What I am attempting to accomplish is take today's date, and if it is
Tuesday through Saturday, the date needs to be equal to the previous
Saturday. If it is Sunday or Monday, it needs to be 2 Saturdays, previous.

Maybe it's just a Monday thing, but I can't seem to determine the correct
statement(s), using DateAdd.

Any help/suggestions are greatly appreciated.

 

[Jerry Whittle]

In a query:

TheSat: IIf(Weekday(Date)= 1 or Weekday(Date)= 2, DateAdd("d", -7
-Weekday(Date), Date), DateAdd("d", -Weekday(Date), Date))

Watch out for word wrapping. It should be all on one line.

One problem: What do you want returned on the upcoming Saturday of 9/19/09?
The above will return 9/5/09

 

[Sharkbyte]

Jerry:

Thanks for the initial help.

For 9/19 I would want it to return 9/12. But 9/20 and 9/21 (Sun & Mon)
should also return 9/12. Tuesday, 9/22 should return 9/19...

I'm not certain, but isn't Microsoft's week Sunday - Saturday, so 9/19 would
be: DateAdd("d", -7, Date) or 9/19 - 7 = 9/12. Correct?

Sharkbyte