Supply Date based on DOW

I have used an existing vb process to supply dates, but I am switching over to a pure SQL. Below is the previous logic.

Select Case Weekday(Date, vbSunday)
Case Is = 1
' We are at a Sunday so we need to go
' to previous Thursday

txtStartDate = DateAdd("d", -3, Date)
Case Is = 2
' We are at a Monday so we need to go
' to previous Thursday

txtStartDate = DateAdd("d", -4, Date)
Case Is = 3
' We are at a Tuesday so we need to go
' to previous Thursday

txtStartDate = DateAdd("d", -5, Date)
Case Is = 4
' We are at a Wednesday so we need to go
' to previous Tuesday

txtStartDate = DateAdd("d", -1, Date)
Case Is = 5
' We are at a Thursday so we need to go
' to previous Tuesday

txtStartDate = DateAdd("d", -2, Date)
Case Is = 6
' We are at a Friday so we need to go
' to previous Tuesday

txtStartDate = DateAdd("d", -3, Date)
Case Is = 7
' We are at a Saturday so we need to go
' to previous Thursday

txtStartDate = DateAdd("d", -2, Date)
Case Else
txtStartDate = Date
End Select

As you can see when current date = Saturday - Tuesday, the query date needs to be the previous Thursday, Otherwise, the previous Tuesday.

For today (27/12/2005) the date would be (22/12/2005).

Thanks for any help!
CT Received on Tue Dec 27 2005 - 08:17:09 CST