ok ... i'm going ballistic
i run an Access Database and need to retrieve records withing a specific timeframe i ask for "startDate" and "endDate" and receive it with the following code:
startDate = cDate(request.queryString("startDate"))
endDate = cDate(request.queryString("endDate"))
ID = request.queryString("ID")
Now this is my Select statement and the dates seem not to work ...:
sqlStrDef = "SELECT dh.RefNr, cr.RefNr, Finish FROM DataHead dh, CritRes cr "
sqlStrDef = sqlStrDef & "WHERE dh.RefNr = cr.RefNr "
sqlStrDef = sqlStrDef & "AND DateCompleted >= #" & startDate & "# And DateCompleted <= #" & endDate & "#"
sqlStrDef = sqlStrDef & "AND dh.Division = '"&strSearch&"' OR dh.Team = '"&strSearch& "'OR dh.CaseOfficer ='"&strSearch& "' "
sqlStrDef = sqlStrDef & "ORDER BY cr.RefNr"
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConn
objCommand.CommandText = sqlStrDef
'executing and resetting to Nothing
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing
I have tryed as well retrieving with the following satement:
sqlStrDef = "SELECT dh.RefNr, cr.RefNr, Finish FROM DataHead dh, CritRes cr "
sqlStrDef = sqlStrDef & "WHERE dh.RefNr = cr.RefNr "
sqlStrDef = sqlStrDef & "AND DateCompleted BETWEEN #" & startDate & "# And <= #" & endDate & "#"
sqlStrDef = sqlStrDef & "AND dh.Division = '"&strSearch&"' OR dh.Team = '"&strSearch& "'OR dh.CaseOfficer ='"&strSearch& "' "
sqlStrDef = sqlStrDef & "ORDER BY cr.RefNr"
Set objCommand = Server.CreateObject("ADODB.Command")
objCommand.ActiveConnection = strConn
objCommand.CommandText = sqlStrDef
'executing and resetting to Nothing
objCommand.CommandType = adCmdText
Set objRS = objCommand.Execute
Set objCommand = Nothing
The results are either nothing or all are selected ... but not the records withing the date range.
where do i go wrong???
Any help would be appreciated
... there is always more to learn...