i have records that i need to select for a specific user by date. The problem is that the date field is actually just a text field. here's my code so far:
set objCnn = Server.CreateObject("ADODB.Connection")
set objRS = Server.CreateObject("ADODB.Recordset")
set last5RS = Server.CreateObject("ADODB.Recordset")
objCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & logfilepath & "Extended Properties=""Text;"""
objRS.CursorLocation=adUseClient
objRS.Open "SELECT DISTINCT TOP 5 SearchText, SearchType, SearchDateTime FROM " & logfileName & " WHERE USERNAME='" & Session("username") & "' AND SEARCHSTATUS='SUCCESS' AND MATCHES > 0 order by SearchDateTime DESC", objCnn, 3,3
This code works, but ofcourse, the dates / times aren't sorted properly because the way it sorts text.
I tried using the convert function:
objRS.Open "SELECT DISTINCT TOP 5 SearchText, SearchType, [SearchDateTime] FROM " & logfileName & " WHERE USERNAME='" & Session("username") & "' AND SEARCHSTATUS='SUCCESS' AND MATCHES > 0 order by Convert(smalldatetime, [SearchDateTime],3)", objCnn, 3,3
but i got an error message that this function was recognized.
any suggestions.?
set objCnn = Server.CreateObject("ADODB.Connection")
set objRS = Server.CreateObject("ADODB.Recordset")
set last5RS = Server.CreateObject("ADODB.Recordset")
objCnn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & logfilepath & "Extended Properties=""Text;"""
objRS.CursorLocation=adUseClient
objRS.Open "SELECT DISTINCT TOP 5 SearchText, SearchType, SearchDateTime FROM " & logfileName & " WHERE USERNAME='" & Session("username") & "' AND SEARCHSTATUS='SUCCESS' AND MATCHES > 0 order by SearchDateTime DESC", objCnn, 3,3
This code works, but ofcourse, the dates / times aren't sorted properly because the way it sorts text.
I tried using the convert function:
objRS.Open "SELECT DISTINCT TOP 5 SearchText, SearchType, [SearchDateTime] FROM " & logfileName & " WHERE USERNAME='" & Session("username") & "' AND SEARCHSTATUS='SUCCESS' AND MATCHES > 0 order by Convert(smalldatetime, [SearchDateTime],3)", objCnn, 3,3
but i got an error message that this function was recognized.
any suggestions.?