[ishaqani]

I have 4 form fields, for Multiple Combination Query. I m getting the below error when i run the query without any values in date range form (Blank Form). or when i run the query using only Impname or Agname, without Date range.

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC Microsoft Access Driver] Syntax error in date in query expression 'IGMDAT BETWEEN ## AND ##'.

Below is the Query Code:

<%
varField3 = Replace(Request("data3"), "'", "''")
Qdate11 = Replace(Request("begin"), "'", "")
Qdate22 = Replace(Request("end"), "'", "")

SQLstr = "SELECT * FROM 652 WHERE IGMDAT BETWEEN #" & Qdate11 & "# AND #" & Qdate22 & "#"
AndString = ""

If varField3 <> "" Then
AndString = AndString & " AND SAGENT LIKE '%" & varField3 & "%'"
End If

If Qdate11 <> "" Then
AndString = AndString & " AND IGMDAT >= #" & Qdate11 & "#"
End If

If Qdate22 <> "" Then
AndString = AndString & " AND IGMDAT <= #" & Qdate22 & "#"
End If

SQLstr = SQLstr & AndString
%>

Plz solve my problem.

[chirag]

Hi there,

You need to change your code like this if you are going to allow null dates in input ...

<%
varField3 = Replace(Request("data3"), "'", "''")
Qdate11 = Replace(Request("begin"), "'", "")
Qdate22 = Replace(Request("end"), "'", "")

if Qdate11 <> "" and Qdate22 <> "" then
SQLstr = "SELECT * FROM 652 WHERE IGMDAT BETWEEN #" & Qdate11 & "#
AND #" & Qdate22 & "#"
AndString = " And "
else
SQLstr = "SELECT * FROM 652 "
AndString = " where "
end if

If varField3 <> "" Then
'''''''note: And is removed from here.....
AndString = AndString & " SAGENT LIKE '%" & varField3 & "%'"
End If

If Qdate11 <> "" Then
AndString = AndString & " AND IGMDAT >= #" & Qdate11 & "#"
End If

If Qdate22 <> "" Then
AndString = AndString & " AND IGMDAT <= #" & Qdate22 & "#"
End If

SQLstr = SQLstr & AndString
%>