Need help creating automated monday dates 1

[Belcebu]

In my access database i have a date in which i have to turn in a report. Here i need to make another field which will tell me a week in advance of that date. Now this is fairly simple with the dateadd function but here is my dilemma:

Lets say my report is due on thursday the 18th. A week in advance would be on thursday the 11th. I need the date generated not to be a thursday but to always be the monday of the previous week. So the date Im looking to have instead of thursday the 11th would be monday the 8th. This way no matter when my report is due(monday, tuesday, wednesday, thursday or friday) the automated date that i need is the monday of the week before the report is due.

Does anyone know how to do this? I'm currently researching but i havent been able to find an answer so far.

I would appreciate any help i can get, thank you all for the support

Belcebu

 

[Eupher]

A few quick question: Is Sunday the first day of the current week or the last day of the previous week? Could the due date ever fall on Saturday or Sunday? And what if the due date is Monday - must the report be generated on the previous Monday, or 2 weeks prior?

Ken S.

 

[Belcebu]

Good questions Ken. Sunday will correspond to the first day of the week and the due dates could fall on saturdays and sundays. So if a report is due on sunday the 14th i will need the automated date to be monday the 8th, the same will go for monday the 15th and saturday the 20th, they all should prompt me to the same monday the 8th.

Here is copy of a calendar days. This might help visualize my problem

Su Mo Tu We Th Fr Sa
7 8 9 10 11 12 13
14 15 16 17 18 19 20

So all the reports due (14th though 20th) will have to prompt me to monday the 8th

Hope this will clear things up

Belcebu

 

[gol4]

?date()-(weekday(date())+5)
7/25/2005
?#8/14/2005#-(weekday(#8/14/2005#)+5)
8/8/2005
?#8/20/2005#-(weekday(#8/20/2005#)+5)
8/8/2005
?#8/22/2005#-(weekday(#8/22/2005#)+5)
8/15/2005

I think thats it

 

[Belcebu]

gol4

Sorry for not responding sooner but i was unable to work at the end of last week. I tried the code that you gave me and it works perfectly!!!. Thank you for the help, and hope this can also help other people with the same problem.

Belcebu