Syntax error (missing operator) in query expression

[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

[neilmc...@gmail.com]

can you post the whole sql string?

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

[D]

neilmc...@gmail.com wrote:
> 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

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.

%>

[neilmc...@gmail.com]

just the resultant sql string please

[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.