SQL Search by Date...

[dodgyone]

I'm trying to produce a list of tasks depending upon which working day it is (Mon to Fri). A variable is send to distinguish which date to use in the week and then the SQL is run...

I'm using the folowing code to get the date

***********************************
WeekBeginning = DateAdd("d", 1 - weekday(date), Date)

Dim arrDate
Dim arrWeekDate(5)
Dim i
For i=1 To 5
arrDate = Split(FormatDateTime(DateAdd("d", i, WeekBeginning),2), "/&quot
arrWeekDate(i) = arrDate(1) & "/" & arrDate(0) & "/" & arrDate(2)
Next

MondayDate = arrWeekDate(1)
TuesdayDate = arrWeekDate(2)
WednesdayDate = arrWeekDate(3)
ThursdayDate = arrWeekDate(4)
FridayDate = arrWeekDate(5)
***********************************

I'm then using these SQL statements but they don't seem to work

***********************************
If Request.QueryString("daytodisplay&quot = "monday" Then
mySQL = "SELECT * FROM Worklist WHERE DateStart Like " & chr(39) & MondayDate & chr(39)& "ORDER BY TimeStart"
ElseIf Request.QueryString("daytodisplay&quot = "tuesday" Then
mySQL = "SELECT * FROM Worklist WHERE DateStart Like " & chr(39) & TuesdayDate & chr(39)& "ORDER BY TimeStart"
ElseIf Request.QueryString("daytodisplay&quot = "wednesday" Then
mySQL = "SELECT * FROM Worklist WHERE DateStart Like " & chr(39) & WednesdayDate & chr(39)& "ORDER BY TimeStart"
ElseIf Request.QueryString("daytodisplay&quot = "thursday" Then
mySQL = "SELECT * FROM Worklist WHERE DateStart Like " & chr(39) & ThursdayDate & chr(39)& "ORDER BY TimeStart"
ElseIf Request.QueryString("daytodisplay&quot = "friday" Then
mySQL = "SELECT * FROM Worklist WHERE DateStart Like " & chr(39) & FridayDate & chr(39)& "ORDER BY TimeStart"
Else
mySQL = "SELECT * FROM Worklist WHERE DateStart Like date() ORDER BY TimeStart"
End If
***********************************

I'm not sure where to go next as I've tried every combinaion I can think of... Thanks for taking the time...

 

[dodgyone]

After tinkering I found the answer...

1. Reverse the date in the loop to show mm/dd/yy:

arrWeekDate(i) = arrDate(0) & "/" & arrDate(1) & "/" & arrDate(2)

2. Use # in the SQL statements as it's a date:

SELECT * FROM Worklist WHERE DateStart Like " & "#" & MondayDate & "#" & " ORDER BY TimeStart;

That's all folks....