Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
gcool12
Starting Member
22 Posts |
 Posted - 2007-12-07 : 17:10:08
|
im showing records on an asp page, filtering by departments worked fine until I got the filter by date to work. Now when i try to filter by department i get this error.
Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'Format([Due_date], 'mm/dd/yyyy') between Format(#11/09/2007#,'mm/dd/yyyy') AND Format(#12/31/2007#,'mm/dd/yyyy') WHERE department = 'ATS''.
/6sigma/5sAudits/pastrecords.asp, line 47
Here is the sql Code:
<%
Dim connection, sql, rs
Set connection = Server.CreateObject ("ADODB.Connection")
connection.open "DSN=5s"
sql = "select distinct departments from departments order by departments"
set rs = server.createObject("ADODB.RecordSet")
rs.Open sql, connection
%>
<%
Dim sql2, rs2, department
department = request("department")
sql2 = "select ID, Department, Due_Date, Auditor, Sort, Set_in_Order, Shine, Standardize, Sustain, Average, Sort_Comment1, " & _
"Sort_Comment2, Sort_Comment3, Sort_Comment4, Sort_Comment5, Set_Comment1, Set_Comment2, Set_Comment3, " & _
"Set_Comment4, Set_Comment5, Shine_Comment1,Shine_Comment2,Shine_Comment3,Shine_Comment4, Shine_Comment5, " & _
"Stand_Comment1, Stand_Comment2, Stand_Comment3, Stand_Comment4, Stand_Comment5, Sustain_Comment1, Sustain_Comment2, " & _
"Sustain_Comment3, Sustain_Comment4, Sustain_Comment5 From 5s_audit Where Format([Due_date], 'mm/dd/yyyy') between Format(#"&start_date&"#,'mm/dd/yyyy') AND Format(#"&end_date&"#,'mm/dd/yyyy') "
if department <> "" and department <> "All" then
sql2 = sql2 & " WHERE department = '" & department & "'"
end if
sql2 = sql2 & " order by department, Due_date"
'Response.write sql2
'Response.end
set rs2 = server.createObject("ADODB.RecordSet")
rs2.Open sql2, connection
%>
Thanks in advance! |
|
|
dataguru1971
Master Smack Fu Yak Hacker
1464 Posts |
Posted - 2007-12-07 : 17:13:56
|
[code]
if department <> "" and department <> "All" then
sql2 = sql2 & " AND department = '" & department & "' "
end if
[/code]
The error is misleading.
The full sql it would appear to print out with the department filter would end up with 2 WHERE statements, which would not work.
Poor planning on your part does not constitute an emergency on my part.
|
 |
|
|
gcool12
Starting Member
22 Posts |
Posted - 2007-12-07 : 17:22:12
|
quote:
Originally posted by dataguru1971
if department <> "" and department <> "All" then
sql2 = sql2 & " AND department = '" & department & "' "
end if
The error is misleading.
The full sql it would appear to print out with the department filter would end up with 2 WHERE statements, which would not work.
Poor planning on your part does not constitute an emergency on my part.
Thanks that worked! |
|
|
|
|
|
|
|
|
Sql Error with Access Database Filter