Date criteria from Monday to Date()

[SDS100UK]

Hi,

I have a query where I want to set the criteria to say:-
From Monday of the current week upto Date()-1.

So when I run it on a Thursday it will give me the date from the Monday to Wednesday.

Many thanks in advance

Steven

 

[tempclerk]

How about Between Date()+1-weekday(Date()) And Date()-1? Or tell the weekday function that Monday is the first day of the week (don't use the function, but have heard it can be done) and skip the "+1".

 

[tempclerk]

Between Date()-weekday(Date(),vbMonday) And Date()-1 should do it; got help from the immediate window.

 

[MichaelRed]

DateAdd("d", vbMonday - Weekday(Date), Date) will always return the 'previous' Monday's date.

Date - 1 will always return yesterdays date.

there will be some problems if you combine these a-la tempclerk's approach and do this on a monday

e.g. between 10/22/2001 and 10/21/2001 which would be the result from Monday Oct. 22, 2001, may produce bad Carma or other job related stress.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[tempclerk]

Good point, Michael. I took the question literally and figured it wasn't going to be used on Monday.