Syntax error (missing operator)

[Tog]

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:

dim fldsize, prodnum
dim conn
prodnum=request("productID")
fldsize=len(prodnum)
set Conn = server.createobject("ADODB.CONNECTION")
Conn.Open "DBQ=" & server.mappath(".../.../db") & "\broomtacklebox.mdb"& ";Driver={Microsoft Access Driver (*.mdb)};"
Conn.execute "DELETE * FROM edirectory WHERE left(Your Product Ref, fldsize)='" & Replace(prodnum, "'", "''") & "'"
Conn.close
set conn = nothing
 

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

[shweta123]

Hi,

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

Shweta

[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

[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

[shweta123]

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