By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,275 Members | 936 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,275 IT Pros & Developers. It's quick & easy.

Syntax error (missing operator)

Tog
P: 11
Hello,

I have read several posts with this heading but none of them have helped.

I have the following error message:

[Microsoft][ODBC Microsoft Access Driver] Syntax error (missing operator) in query expression 'left(Your Product Ref, fldsize)='4341''.
/control/productoptions_delete.asp, line 38
=======================================

the code is:
Expand|Select|Wrap|Line Numbers
  1. dim fldsize, prodnum
  2. dim conn
  3. prodnum=request("productID")
  4. fldsize=len(prodnum)
  5. set Conn = server.createobject("ADODB.CONNECTION")
  6. Conn.Open "DBQ=" & server.mappath(".../.../db") & "\broomtacklebox.mdb"& ";Driver={Microsoft Access Driver (*.mdb)};"
  7. Conn.execute "DELETE * FROM edirectory WHERE left(Your Product Ref, fldsize)='" & Replace(prodnum, "'", "''") & "'"
  8. Conn.close
  9. set conn = nothing
  10.  
line 38 is the one that starts "Conn Execute"

I have tried several different combinations of apostrophe and double apostrophe but keep getting the same error. The field in the access database is a text field, not a number field.

Can anyone offer a solution?

Regards

Tog
Feb 13 '07 #1
Share this Question
Share on Google+
4 Replies


shweta123
Expert 100+
P: 692
Hi,

Write the query as Delete from instead of Delete * from..............

Shweta
Feb 13 '07 #2

Tog
P: 11
Tog
I tried that, but it does not make any difference.

I wonder if it could be because I am trying to match only part of a field?

I am lost and keep trying different things but without success.

I am sure the answer is simple, but can't put my finger on it.

Tog
Feb 13 '07 #3

Tog
P: 11
Tog
OK folks,

the answer was that the field name had spaces in it.

It appears that queries cannot be made using fields with spaces in their names.

I replaced the spaces with underscores and it worked ok.

Regards

Tog
Feb 13 '07 #4

shweta123
Expert 100+
P: 692
Hi,

If Prodnum is numeric value don't enclose it within quotes.
try

"DELETE * FROM edirectory WHERE left(Your Product Ref, fldsize)=" & Replace(prodnum, "'", "''") & ""

OR

check for Your Product Ref ,it may be containing null value for some record.


Shweta
Feb 13 '07 #5

Post your reply

Sign in to post your reply or Sign up for a free account.