first and end of this week

[hair]

I needed the first and the last day of the week. I wrote this:

Private Sub Commande10_Click()
Select Case semaineencours

Case Weekday(Now) = 0
Me.datedebut = Now - 6
Me.datefin = Now
Case Weekday(Now) = 1
Me.datedebut = Now
Me.datefin = Now + 6
Case Weekday(Now) = 2
Me.datedebut = Now - 1
Me.datefin = Now + 5
Case Weekday(Now) = 3
Me.datedebut = Now - 2
Me.datefin = Now + 4
Case Weekday(Now) = 4
Me.datedebut = Now - 2
Me.datefin = Now + 4
Case Weekday(Now) = 5
Me.datedebut = Now - 4
Me.datefin = Now + 2
Case Weekday(Now) = 6
Me.datedebut = Now - 5
Me.datefin = Me.datedebut + 1

End Select

End Sub


It is working just fine.

The Question : is there a simpler way to accomplish this task?

(that's my eternal problem)

 

[hair]

actualy it was

case weekday(now)
case 1
blabla
case 2
blabla
..etc

 

[Mile-O]

Provided that the first day is a Monday:

First Day of Week:

=Date()+IIf(2<Weekday(Date()),7-Weekday(Date())+2,2-Weekday(Date()))-7

Last Day of Week:

=Date()+IIf(2<Weekday(Date()),7-Weekday(Date())+2,2-Weekday(Date()))-1

 

[Mile-O]

There are better expressions tucked away in other threads.

 

[raskew]

The following will return the start or end date of a week, based on the start day specified by the user.

Function FirstOrLastDay(pMyDate As Date, pFirstDay As Integer, Optional isFirst As Boolean = True) As Date
'*******************************************
'Purpose:   Display the start or end date of the week in which pMyDate falls.
'
'Reference: [url]http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=58217[/url]
'
'Notes:     1)  pFirstDay: 1 (Sunday) to 7 (Saturday)
'           2)  Function defaults to the start date if isFirst is omitted.
'
'Inputs:    from debug (immediate) window
'           1)  ? FirstOrLastDay(#11/28/03#, 2, True)<enter>
'           2)  ? FirstOrLastDay(#11/28/03#, 2, False)<enter>
'
'Outputs:   1) 11/24/03
'           2) 11/30/03
'*******************************************
Dim datehold As Date

isFirst = IIf(IsMissing(isFirst), True, isFirst)

datehold = IIf(WeekDay(pMyDate) = pFirstDay, pMyDate, pMyDate - (WeekDay(pMyDate) _
+ IIf(WeekDay(pMyDate) <= pFirstDay, 7, 0) - pFirstDay))

FirstOrLastDay = IIf(isFirst, datehold, datehold + 6)

End Function