I have an ASP form with two date fields (FromDate and ToDate).
I get this error.
Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'dtDate >= #1# and dtDate <=#1#'.
/Fast2/WkTotalOrders.asp, line 27
Here's the code for my results page.
<%@LANGUAGE="VBSCRIPT" CODEPAGE="1252"%>
<%
Dim Recordset1__MMColParam
Recordset1__MMColParam = "1"
If (Request.QueryString("FromDate") <> "") Then
Recordset1__MMColParam = Request.QueryString("FromDate")
End If
%>
<%
Dim Recordset1__MMColParam2
Recordset1__MMColParam2 = "1"
If (Request.QueryString("ToDate") <> "") Then
Recordset1__MMColParam2 = Request.QueryString("ToDate")
End If
%>
<%
Dim Recordset1
Dim Recordset1_numRows
Set Recordset1 = Server.CreateObject("ADODB.Recordset")
Recordset1.ActiveConnection = MM_Fast2_STRING
Recordset1.Source = "SELECT * FROM qryDailyOrderTotals WHERE dtDate >= #" + Replace(Recordset1__MMColParam, "#", "##") + "# and dtDate <=#" + Replace(Recordset1__MMColParam2, "#", "##") + "#"
Recordset1.CursorType = 0
Recordset1.CursorLocation = 2
Recordset1.LockType = 1
Recordset1.Open()
Recordset1_numRows = 0
%>
<%
Dim Recordset2__MMColParam
Recordset2__MMColParam = "1"
If (Request.QueryString("FromDate") <> "") Then
Recordset2__MMColParam = Request.QueryString("FromDate")
End If
%>
<%
Dim Recordset2__MMColParam2
Recordset2__MMColParam2 = "1"
If (Request.QueryString("ToDate") <> "") Then
Recordset2__MMColParam2 = Request.QueryString("ToDate")
End If
%>
<%
Dim Recordset2
Dim Recordset2_numRows
Set Recordset2 = Server.CreateObject("ADODB.Recordset")
Recordset2.ActiveConnection = MM_Fast2_STRING
Recordset2.Source = "SELECT * FROM qryDailyPartTotals WHERE dtDate >= #" + Replace(Recordset2__MMColParam, "#", "##") + "# and dtDate <= #" + Replace(Recordset2__MMColParam2, "#", "#") + "#"
Recordset2.CursorType = 0
Recordset2.CursorLocation = 2
Recordset2.LockType = 1
Recordset2.Open()
Recordset2_numRows = 0
%>
<%
Dim Recordset3__MMColParam
Recordset3__MMColParam = "1"
If (Request.QueryString("FromDate") <> "") Then
Recordset3__MMColParam = Request.QueryString("FromDate")
End If
%>
<%
Dim Recordset3__MMColParam2
Recordset3__MMColParam2 = "1"
If (Request.QueryString("ToDate") <> "") Then
Recordset3__MMColParam2 = Request.QueryString("ToDate")
End If
%>
<%
Dim Recordset3
Dim Recordset3_numRows
Set Recordset3 = Server.CreateObject("ADODB.Recordset")
Recordset3.ActiveConnection = MM_Fast2_STRING
Recordset3.Source = "SELECT * FROM qryDailyTimeTotals WHERE dtDate >= #" + Replace(Recordset3__MMColParam, "#", "##") + "# and dtDate<=#" + Replace(Recordset3__MMColParam2, "#", "##") + "#"
Recordset3.CursorType = 0
Recordset3.CursorLocation = 2
Recordset3.LockType = 1
Recordset3.Open()
Recordset3_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index
Repeat1__numRows = -1
Repeat1__index = 0
Recordset1_numRows = Recordset1_numRows + Repeat1__numRows
%>
<%
Dim Repeat2__numRows
Dim Repeat2__index
Repeat2__numRows = -1
Repeat2__index = 0
Recordset2_numRows = Recordset2_numRows + Repeat2__numRows
%>
<%
Dim Repeat3__numRows
Dim Repeat3__index
Repeat3__numRows = -1
Repeat3__index = 0
Recordset3_numRows = Recordset3_numRows + Repeat3__numRows
%>
<SCRIPT RUNAT=SERVER LANGUAGE=VBSCRIPT>
function DoDateTime(str, nNamedFormat, nLCID)
dim strRet
dim nOldLCID
strRet = str
If (nLCID > -1) Then
oldLCID = Session.LCID
End If
On Error Resume Next
If (nLCID > -1) Then
Session.LCID = nLCID
End If
If ((nLCID < 0) Or (Session.LCID = nLCID)) Then
strRet = FormatDateTime(str, nNamedFormat)
End If
If (nLCID > -1) Then
Session.LCID = oldLCID
End If
DoDateTime = strRet
End Function
</SCRIPT>
<html>
<head>
<title>Weekly Totals</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body bgcolor="#FFFFFF">
<p align="center"><strong>Fast Incoming Order Totals</strong></p>
<p align="center"><%=(Recordset1.Fields.Item("dtDate" ).Value)%></p>
<table width="700" border="0" align="center" bgcolor="#FFFFFF">
<tr bgcolor="#FFCC00" >
<td width="14" height="21" bgcolor="#FFFFFF">
<div align="center"></div></td>
<td width="286" bgcolor="#FFFFFF">
<div align="center"></div></td>
<td width="174" bgcolor="#FFFFFF">
<div align="center"><strong>Orders</strong></div></td>
<td width="176" bgcolor="#FFFFFF">
<div align="center"></div>
<div align="center"></div>
<div align="center"><strong>Parts </strong></div></td>
<td width="6" bgcolor="#FFFFFF"> </td>
<td width="165" bgcolor="#FFFFFF">
<div align="center"></div>
<div align="center"></div>
<div align="center"><strong>Time </strong></div></td>
<td width="47" bgcolor="#FFFFFF"> </td>
</tr>
<tr >
<td height="20" colspan="3" bgcolor="#FFFFFF">
<table width="370" border="1">
<% While ((Repeat1__numRows <> 0) AND (NOT Recordset1.EOF)) %>
<tr bgcolor="#FFFFFF" >
<td width="160" height="20">
<strong><%=(Recordset1.Fields.Item("CatType").Valu e)%></strong><div align="center">
</strong></div></td>
<td width="100">
<div align="center"><strong></strong></div>
</td>
<td width="160" height="20"><strong><%=(Recordset1.Fields.Item("Em pName").Value)%></strong></td>
<td width="50" height="20">
<div align="center"><%=(Recordset1.Fields.Item("CountOf Part").Value)%></div></td>
</tr>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
Recordset1.MoveNext()
Wend
%>
</table></td>
<td height="25" colspan="2" bgcolor="#FFFFFF">
<table width="105" border="1" cellpadding="1" cellspacing="2" bgcolor="#FFFFFF">
<% While ((Repeat2__numRows <> 0) AND (NOT Recordset2.EOF)) %>
<tr>
<td height="20" bgcolor="#FFFFFF">
<div align="center"></div>
<div align="center"></div>
<div align="center"></div>
<div align="center"><%=(Recordset2.Fields.Item("SumOfPa rt").Value)%></div></td>
</tr>
<%
Repeat2__index=Repeat2__index+1
Repeat2__numRows=Repeat2__numRows-1
Recordset2.MoveNext()
Wend
%>
</table></td>
<td height="20" colspan="2" bgcolor="#FFFFFF">
<table width="105" border="1">
<% While ((Repeat3__numRows <> 0) AND (NOT Recordset3.EOF)) %>
<tr>
<td height="20" bgcolor="#FFFFFF">
<div align="center"></div>
<div align="center"></div>
<div align="center"></div>
<div align="center"><%= DoDateTime((Recordset3.Fields.Item("SumOfTimeDiff" ).Value), 3, 2057) %></div>
<div align="center"></div></td>
</tr>
<%
Repeat3__index=Repeat3__index+1
Repeat3__numRows=Repeat3__numRows-1
Recordset3.MoveNext()
Wend
%>
</table></td>
</tr>
</table>
<p align="center"><strong></strong></p>
</body>
</html>
<%
Recordset1.Close()
Set Recordset1 = Nothing
%>
<%
Recordset2.Close()
Set Recordset2 = Nothing
%>
<%
Recordset3.Close()
Set Recordset3 = Nothing
%>
SQL problem in ASP
