Syntax error (missing operator) in query expression

D

D

I think this is a simple fix, but a second set of eyes could certainly
help. Here is the error in it's entirety followed by my search criteria
code. Any suggestions?

Microsoft JET Database Engine error '80040e14'

Syntax error (missing operator) in query expression 'LimitDate LIKE
#7/18# WHERE skill in (select skill from tbl_timelimit where skill LIKE
'%sales%' ) AND LimitDate >= #7/18/2006#'.

/tier1overtime/overtime/TimeList.asp, line 220

'Build dynamic sql.
sql = "select * from tbl_TimeLimit "

'--Name (partial and exact search)
'If Not IsEmpty(reqname) Then
if reqname<>"" then
Dim strName
strName = Trim(reqname)
If strName <> "" Then
'Test for WHERE
If blnWhere Then sql = sql & " AND " Else sql = sql & " WHERE " :
blnWhere = True
sql = sql & "LimitDate LIKE #" & strName & "# "
End If
End If

'--Name (partial and exact search)
'If Not IsEmpty(reqcategory) Then
if reqcategory<>"" then
strName = Trim(reqcategory)
If strName <> "" Then
'Test for WHERE
sql = sql & " WHERE skill in (select skill from tbl_timelimit where "
: blnWhere = True
If (Left(strName, 1) = "*" And Len(strName) > 1) Then 'Partial search
sql = sql & "skill LIKE '%" & Replace(Mid(strName, 2), "'", "''") &
"' "
ElseIf (Right(strName, 1) = "*" And Len(strName) > 1) Then 'Partial
search
sql = sql & "skill LIKE '" & Replace(Mid(strName, 1,
Len(strName)-1), "'", "''") & "%' "
Else 'Exact match
sql = sql & "skill LIKE '%" & Replace(strName, "'", "''") & "%' "
End If
sql = sql & ") "
End If
End If


If blnWhere Then sql = sql & " AND " Else sql = sql & " WHERE " :
blnWhere = True
sql = sql & "LimitDate >= #" & date & "# "
sql = sql & " ORDER BY LimitDate ASC"

session("tmpSqlExcel")= sql
 
N

neilmcguigan

can you post the whole sql string?

looks like there is a where criterion before the where clause...
 
D

D

can you post the whole sql string?

looks like there is a where criterion before the where clause...

<%
'Constants declared
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = &H0001
Dim PAGE_SIZE
PAGE_SIZE = dbRecordsPerPage 'The size of our pages.


'Variable Declaration.
Dim strURL ' The URL of this page so the form will work
Dim rstSearch ' ADO recordset
Dim reqname ' The Course text being looked for
Dim reqCategory ' The Category text being looked for
Dim iPageCurrent ' The page we're currently on
Dim iPageCount ' Number of pages of records
Dim iRecordCount ' Count of the records returned
Dim I ' Standard looping variable
Dim blnWhere
Dim cnt

blnWhere = False

' Retreive the URL of this page from Server Variables
strURL = Pub_Server_Name & request.ServerVariables("HTTP_HOST") &
request.ServerVariables("URL")

' Retreive the term being searched for.
reqname = Request.QueryString("txtname")
reqname = Replace(reqname, "'", "''")
if IsDate(reqname) then
reqname = reqname
else
reqname = ""
end if

' Retreive the term being searched for.
reqcategory = Request.QueryString("txtcategory")
reqcategory = Replace(reqcategory, "'", "''")

' Retrieve page to show or default to the first
If Request.QueryString("page") = "" Then
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page"))
End If

'Build dynamic sql.
sql = "select * from tbl_TimeLimit "

'--Name (partial and exact search)
'If Not IsEmpty(reqname) Then
if reqname<>"" then
Dim strName
strName = Trim(reqname)
If strName <> "" Then
'Test for WHERE
If blnWhere Then sql = sql & " AND " Else sql = sql & " WHERE " :
blnWhere = True
sql = sql & "LimitDate LIKE #" & strName & "# "
End If
End If

'--Name (partial and exact search)
'If Not IsEmpty(reqcategory) Then
if reqcategory<>"" then
strName = Trim(reqcategory)
If strName <> "" Then
'Test for WHERE
sql = sql & " WHERE skill in (select skill from tbl_timelimit where "
: blnWhere = True
If (Left(strName, 1) = "*" And Len(strName) > 1) Then 'Partial search
sql = sql & "skill LIKE '%" & Replace(Mid(strName, 2), "'", "''") &
"' "
ElseIf (Right(strName, 1) = "*" And Len(strName) > 1) Then 'Partial
search
sql = sql & "skill LIKE '" & Replace(Mid(strName, 1,
Len(strName)-1), "'", "''") & "%' "
Else 'Exact match
sql = sql & "skill LIKE '%" & Replace(strName, "'", "''") & "%' "
End If
sql = sql & ") "
End If
End If


If blnWhere Then sql = sql & " AND " Else sql = sql & " WHERE " :
blnWhere = True
sql = sql & "LimitDate >= #" & date & "# "
sql = sql & " ORDER BY LimitDate ASC"

session("tmpSqlExcel")= sql

' Execute our query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
Set rstSearch = Server.CreateObject("ADODB.Recordset")
rstSearch.PageSize = PAGE_SIZE
rstSearch.CacheSize = PAGE_SIZE

' Open our recordset
rstSearch.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText
'Response.Write sql & "<BR>"


' Get a count of the number of records and pages
' for use in building the header and footer text.
iRecordCount = rstSearch.RecordCount
iPageCount = rstSearch.PageCount


Dim iPageMax
Dim iPageMin
Dim iPageOffSet
iPageOffSet = 5

if iPageCurrent < 5 then
iPageOffSet = 5 + (5-iPageCurrent)
end if

if (iPageCurrent + iPageOffSet) > iPageCount then
iPageMax = iPageCurrent + (iPageCount - iPageCurrent)
else
iPageMax = iPageCurrent + iPageOffSet
end if

if (iPageCurrent - iPageOffSet) < 1 then
iPageMin = 1
else
if (iPageCurrent + iPageOffSet) > iPageCount then
iPageMin = ((iPageCount - (iPageOffSet + iPageOffSet )) + 1)
else
iPageMin = (iPageCurrent - iPageOffSet) + 1
end if
end if
if iPageMin < 1 then iPageMin = 1
if iPageMax > iPageCount then iPageMax = iPageCount


If iRecordCount = 0 Then
' Display no records error.
Else
'Move to the page we need to show.
rstSearch.AbsolutePage = iPageCurrent
End if

' Display a table of the data in the recordset. We loop through the
' recordset displaying the fields from the table and using MoveNext
' to increment to the next record. We stop when we reach EOF.

%>
 
N

neilmcguigan

just the resultant sql string please

<%
'Constants declared
Const adOpenStatic = 3
Const adLockReadOnly = 1
Const adCmdText = &H0001
Dim PAGE_SIZE
PAGE_SIZE = dbRecordsPerPage 'The size of our pages.


'Variable Declaration.
Dim strURL ' The URL of this page so the form will work
Dim rstSearch ' ADO recordset
Dim reqname ' The Course text being looked for
Dim reqCategory ' The Category text being looked for
Dim iPageCurrent ' The page we're currently on
Dim iPageCount ' Number of pages of records
Dim iRecordCount ' Count of the records returned
Dim I ' Standard looping variable
Dim blnWhere
Dim cnt

blnWhere = False

' Retreive the URL of this page from Server Variables
strURL = Pub_Server_Name & request.ServerVariables("HTTP_HOST") &
request.ServerVariables("URL")

' Retreive the term being searched for.
reqname = Request.QueryString("txtname")
reqname = Replace(reqname, "'", "''")
if IsDate(reqname) then
reqname = reqname
else
reqname = ""
end if

' Retreive the term being searched for.
reqcategory = Request.QueryString("txtcategory")
reqcategory = Replace(reqcategory, "'", "''")

' Retrieve page to show or default to the first
If Request.QueryString("page") = "" Then
iPageCurrent = 1
Else
iPageCurrent = CInt(Request.QueryString("page"))
End If

'Build dynamic sql.
sql = "select * from tbl_TimeLimit "

'--Name (partial and exact search)
'If Not IsEmpty(reqname) Then
if reqname<>"" then
Dim strName
strName = Trim(reqname)
If strName <> "" Then
'Test for WHERE
If blnWhere Then sql = sql & " AND " Else sql = sql & " WHERE " :
blnWhere = True
sql = sql & "LimitDate LIKE #" & strName & "# "
End If
End If

'--Name (partial and exact search)
'If Not IsEmpty(reqcategory) Then
if reqcategory<>"" then
strName = Trim(reqcategory)
If strName <> "" Then
'Test for WHERE
sql = sql & " WHERE skill in (select skill from tbl_timelimit where "
: blnWhere = True
If (Left(strName, 1) = "*" And Len(strName) > 1) Then 'Partial search
sql = sql & "skill LIKE '%" & Replace(Mid(strName, 2), "'", "''") &
"' "
ElseIf (Right(strName, 1) = "*" And Len(strName) > 1) Then 'Partial
search
sql = sql & "skill LIKE '" & Replace(Mid(strName, 1,
Len(strName)-1), "'", "''") & "%' "
Else 'Exact match
sql = sql & "skill LIKE '%" & Replace(strName, "'", "''") & "%' "
End If
sql = sql & ") "
End If
End If


If blnWhere Then sql = sql & " AND " Else sql = sql & " WHERE " :
blnWhere = True
sql = sql & "LimitDate >= #" & date & "# "
sql = sql & " ORDER BY LimitDate ASC"

session("tmpSqlExcel")= sql

' Execute our query using the connection object. It automatically
' creates and returns a recordset which we store in our variable.
Set rstSearch = Server.CreateObject("ADODB.Recordset")
rstSearch.PageSize = PAGE_SIZE
rstSearch.CacheSize = PAGE_SIZE

' Open our recordset
rstSearch.Open sql, cn, adOpenStatic, adLockReadOnly, adCmdText
'Response.Write sql & "<BR>"


' Get a count of the number of records and pages
' for use in building the header and footer text.
iRecordCount = rstSearch.RecordCount
iPageCount = rstSearch.PageCount


Dim iPageMax
Dim iPageMin
Dim iPageOffSet
iPageOffSet = 5

if iPageCurrent < 5 then
iPageOffSet = 5 + (5-iPageCurrent)
end if

if (iPageCurrent + iPageOffSet) > iPageCount then
iPageMax = iPageCurrent + (iPageCount - iPageCurrent)
else
iPageMax = iPageCurrent + iPageOffSet
end if

if (iPageCurrent - iPageOffSet) < 1 then
iPageMin = 1
else
if (iPageCurrent + iPageOffSet) > iPageCount then
iPageMin = ((iPageCount - (iPageOffSet + iPageOffSet )) + 1)
else
iPageMin = (iPageCurrent - iPageOffSet) + 1
end if
end if
if iPageMin < 1 then iPageMin = 1
if iPageMax > iPageCount then iPageMax = iPageCount


If iRecordCount = 0 Then
' Display no records error.
Else
'Move to the page we need to show.
rstSearch.AbsolutePage = iPageCurrent
End if

' Display a table of the data in the recordset. We loop through the
' recordset displaying the fields from the table and using MoveNext
' to increment to the next record. We stop when we reach EOF.

%>
 
D

D

Believe it or not Neil just your posting helped to jog my brain cells
again. Thanks. I resolved this issue :) You were correct I had my
criteria before my clause. Changed and working.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,743
Messages
2,569,478
Members
44,899
Latest member
RodneyMcAu

Latest Threads

Top