Find a week ago Sat

[cpberg1]

Hi all, New VBA user, Access 2003, Win 7/XP

The code I set up below fits my need but I can imagine is grossly inefficient if it were to be a much larger scale set of VBA instructions. How would the experts approach this? I also thought of using a select case statement. For what I'm doing the code is just fine but hoping to learn more!

Private Sub Form_Load()
On Error GoTo Err_Form_Load
'Find a week ago Sun to populate tbStartDate field
'Find a week ago Sat to populate tbStartDate field
If Weekday(Date) = vbSaturday Then
Me.tbStartDate = Date - 13
Me.tbEndDate = Date - 6
Else
End If
    If Weekday(Date) = vbSunday Then
    Me.tbStartDate = Date - 7
    Me.tbEndDate = Date
    Else
    End If
        If Weekday(Date) = vbMonday Then
        Me.tbStartDate = Date - 8
        Me.tbEndDate = Date - 1
        Else
        End If
            If Weekday(Date) = vbTuesday Then
            Me.tbStartDate = Date - 9
            Me.tbEndDate = Date - 2
            Else
            End If
                If Weekday(Date) = vbWednesday Then
                Me.tbStartDate = Date - 10
                Me.tbEndDate = Date - 3
                Else
                End If
                    If Weekday(Date) = vbThursday Then
                    Me.tbStartDate = Date - 11
                    Me.tbEndDate = Date - 4
                    Else
                    End If
                        If Weekday(Date) = vbFriday Then
                        Me.tbStartDate = Date - 12
                        Me.tbEndDate = Date - 5
                        End If

Best,
Chris

 

[boblarson]

Here's a function you can use.

http://www.btabdevelopment.com/ts/default.aspx?PageId=39