473,322 Members | 1,522 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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

Jul 18 '06 #1
4 1891
can you post the whole sql string?

looks like there is a where criterion before the where clause...
D wrote:
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
Jul 18 '06 #2
D

ne**********@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

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

%>

Jul 18 '06 #3
just the resultant sql string please
D wrote:
ne**********@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

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

%>
Jul 18 '06 #4
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.

Jul 18 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

29
by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules"...
4
by: dcarson | last post by:
I've read about this error in several other discussions, but still can't seem to pinpoint the problem with my code. Everything seemed to be working fine for some time, but it now tends to bomb out...
3
by: Lumpierbritches | last post by:
Syntax Error (missing operator) in query expression =BLANK'S MOLLIE-PRINCE BRUISER-3/14/2004-03 AnimalID is correct. I'm trying to open with a command button or double click the frmAnimal from a...
4
by: khan | last post by:
getting, error, 3075 Syntax Error (Missing Operator) in query expression '8WHERE .=1' Dim TotalQty As Integer TotalQty = DLookup("", "", "=" & Forms!!)
24
by: deko | last post by:
I'm trying to log error messages and sometimes (no telling when or where) the message contains a string with double quotes. Is there a way get the query to insert the string with the double...
4
by: T. Wintershoven | last post by:
Hi Can someone please tell me whats wrong with the last line of the query below. The first three lines work fine but when i add the fourth line i get an error message (see text at ERROR...
7
by: John Øllgård Jensen | last post by:
Hi Using MS Asccess 2000: In a query I'm trying to create a new field with following expression: FilmDate: Left(,4) The field "FilmNo" is another text field in the query. This is...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.