Return Date of Previous Day of Week

[Pisteuo]

I would like a field's default value to return the date of the previous Monday.

For example:
Now = Tuesday, 08-Apr-14
Return Monday, 07-Apr-14

Further example:
Now = Saturday, 12-Apr-14
Return Monday, 07-Apr-14

Thanks for the help.

 

[spikepl]

Look in the Date functions for the necessary bits - http://www.techonthenet.com/access/functions/

 

[Keith Tedbury]

Date()-Weekday(Date())+2

date() gives todays date, Weekday(date()) gives the value of the weekday which you take away from the date and 2 is the value for Monday so by adding it you get the date for the last Monday.

I would be interested in seeing how other people would do it and if there is a better way(normally is).

 

[gemma-the-husky]

I never quite understand how your slick method works, well enough to write it without having to spend time looking it up!

I just tend to do this brute force approach for simplicity

 newdate = {whatever start date}
 while weekday(newdate)<>vbmonday
    newdate=newdate-1
 wend

 

[pr2-eugin]

Keith_Tedbury, your code fails for Sunday?

? Date()-Weekday(Date())+2
07/04/2014 
? #13/04/2014#-Weekday(#13/04/2014#)+2
14/04/2014

It gives the value for next Monday. I would suggest this modification.

Public Function getMonDate(Optional inputDate) As Date
[COLOR=Green]'********************
'Code Courtesy of
'  Paul Eugin
'********************[/COLOR]
    If IsMissing(inputDate) Or IsNull(inputDate) Then inputDate = Date
    
    getMonDate = inputDate - Weekday(inputDate, vbMonday) + 1
End Function

Dave's code would also be an alternative. As it only loops through a limited period (maximum of 6 days) there would not be a huge performance hit.

 

[Keith Tedbury]

Hi Paul

Thanks for pointing that out and giving an explanation.

Gone back to look at where I have used the code and saw that I actually use a if statement to check if its a Sunday and if it take away 6 days. Kind of places I have used it is in the default value for date boxes used to filter reports.

=IIf(Weekday(Date())=1,Date()-6,Date()-Weekday(Date())+2)

Always find it interesting to see how other people would and have done things and to learn from it.

 

[Brianwarnock]

Use the second parameter of weekday to make Monday the first day of the week

In VBA

Date()-Weekday( Date(),vbMonday)+1

In SQL/Query replace vbMonday by 1 I think, it will be in help

Brian