comparing date/time to text Data Type

[chmillion]

I'm new to this so please be patient. I'm trying to compare a "text" vs "date/time" data type. I thought it would be possible if I could create a variable containing the current date and converted it to a string (first 6 lines below) I would then be able to make the comparison but I'm not receiving any info from the database. I think it may be my syntax in my sql statement. Any ideas?

<%
Dim m, d, y, i, newDate
m = MonthName(Month(Now()))
d = Day(Now())
y = Year(Now())
i = m & " " & d & "," & " "& y
newDate = CStr(i)

openstr = "driver={Microsoft Access Driver (*.mdb)}; dbq=" & Server.MapPath("mcxweb.mdb")
Set cn = Server.CreateObject("ADODB.Connection")
cn.Open openstr

sql = "SELECT tbl_TourRequests.startDate FROM tbl_TourRequests WHERE (tbl_TourRequests.startDate) = '"& newDate & "';"

Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql, cn, 3, 3

On Error Resume Next
rs.MoveFirst

Response.Write("<table cellpadding=0 cellspacing=0 border=0>" & vbcrlf)
Response.Write(" <tr bgcolor=#006699><td class=b12wht colspan=2>Welcome Today's Product Groups</td>" & vbcrlf)
Response.Write(" <tr><td colspan=2><img src=""Images/MyPixel.gif"" height=10 width=1 border=0></td></tr>" & vbcrlf)

Do While Not rs.EOF
Response.Write(" <tr><td>&nbsp;</td><td class=b12wht>" & Server.HTMLEncode<rs.Fields("startDate")) & "</td></tr>" & vbcrlf)

rs.MoveNext
Loop

Response.Write("</table>")

rs.Close
Set rs = Nothing

cn.Close
Set cn = Nothing

%>

[mattglet]

I'm not sure if this will work with your setup, but should start to point you in the right direction:

sql = "SELECT tbl_TourRequests.startDate FROM tbl_TourRequests WHERE (tbl_TourRequests.startDate) = CONVERT(datetime, '"& newDate & "', 107);"

If you could provide the format of how tbl_TourRequests.startDate is stored, we could probably get you exactly what you need. I'm assuming it's in mon dd, yyyy because you went through the trouble to make sure newDate was in that format.

-Matt

[chmillion]

tbl_TourRequests.startDate is stored as a "text" data type in an Access Dbase as; May 25, 2004 - 9:30 AM. Not sure why the previous dev created the field as a "text" data type.

I went through the trouble of formatting so the variable would be similar to the first part of the field ("May 25, 2004) and then I would be able to use a LIKE statement to make the comparison.