Find records that contain dates of next Tuesday and onward

[darnnnel]

Hi,

I have a date field called StartDate, i am running a query in VBA to find any records with a StartDate of next Tuesday and onward. This the code i have, and its not working. Any ideas?

Private Sub cmdFuture_Click()

Dim sSQL As String

sSQL = "SELECT * FROM QueryCases WHERE Year(QueryCases.StartDate)* 53 + DatePart("ww", QueryCases.StartDate) = Year(Date())* 53 + DatePart("ww", Date()) + 3 ORDER BY QueryCases.StartDate;"

Me![ViewCasesSubform].Form.RecordSource = sSQL

End Sub

Thanks

[jaxjagfan]

Hi,

I have a date field called StartDate, i am running a query in VBA to find any records with a StartDate of next Tuesday and onward. This the code i have, and its not working. Any ideas?

Private Sub cmdFuture_Click()

Dim sSQL As String

sSQL = "SELECT * FROM QueryCases WHERE Year(QueryCases.StartDate)* 53 + DatePart("ww", QueryCases.StartDate) = Year(Date())* 53 + DatePart("ww", Date()) + 3 ORDER BY QueryCases.StartDate;"

Me![ViewCasesSubform].Form.RecordSource = sSQL

End Sub

Thanks

May need some adjustments depending on your definition of next Tuesday. In all of your records if startdate is 11/15/2007 for one record and 1/3/2008 for another record then next Tuesday would be different for each record.

If you have a control on your form for entering or selecting a date (or simply the next Tuesday from today (Now()). Then my WHERE clause remains the same you will have to redefine StartDate (Set i = Weekday(Me.txtDate)). "txtdate" could have a default value of Date() - today's date.

Private Sub cmdFuture_Click()

Dim sSQL As String
Dim i as int
Dim nxtDate as Date

Set i = WeekDay(StartDate)
Select Case i
Case 1
nxtDate = StartDate + 2 ' Today is Sunday - Tuesday is in 2 days or adjust code for next Tuesday add 9
Case 2
nxtDate = StartDate + 1 ' Today is Monday - Tuesday is tomorrow or adjust code for next Tuesday add 8
Case 3
nxtDate = StartDate + 7 ' Today is Tuesday - go to next Tuesday
Case 4
nxtDate = StartDate + 6 ' Today is Wednesday - go to next Tuesday
Case 5
nxtDate = StartDate + 5 ' Today is Thursday - go to Tuesday
Case 6
nxtDate = StartDate + 4 ' Today is Friday - go to Tuesday
Case 7
nxtDate = StartDate + 3 ' Today is Saturday - go to Tuesday
End Select

sSQL = "SELECT * FROM QueryCases WHERE (QueryCases.StartDate) >="& nxtDate & ";"

Me![ViewCasesSubform].Form.RecordSource = sSQL

End Sub

[missinglinq]

For clarification purposes, when you say "with a StartDate of next Tuesday and onward" do you mean

from tomorrow onward, if today is Monday

or

from Tuesday of next week and onward?

Tuesday of next week, for instance would be:

(date + (7 - Weekday(date, 3)) + 1)

Linq ;0)>

[darnnnel]

Thanks for the reply. I mean from Tuesday of next week and onward. The code above worked great!
Thanks

[missinglinq]

QueryCases.SatrtDate > (date + (7 - Weekday(date, 3)) + 1)

Linq ;0)>