Sql Error with Access Database Filter

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.

All Forums

Other Forums

MS Access

Sql Error with Access Database Filter

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!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources