CODEWITHSUNDEEP
sqlms-accessvbasyntax-error
Flat Cat
Flat Cat

Reputation: 916

VBA Update Statement - Missing Operator in Query Expression

First, I know that this question has been asked on this forum already, but each previous question differs from my situation and the solutions don't work. My commas are all there and I'm using a very simple query. Moving right along:

I'm using a linked table in Access, which links to a SQL Server database. Everything works except this query. If I run the text directly in SQL Server, it runs fine. Therefore, the syntax of the query must be fine.

Here's what I'm running:

CurrentDb.Execute "Update dbo_TS_Activity Set [Remarks] = ''Updated Remarks''   Where [id] = 1124 AND [Emp_Name] = ''CONFUSED''  AND [Approved] = 0"

I get Run-time error '3075' Syntax error (missing operator) in query expression ''Updated Remarks''

What I've tried:

Upvotes: 0

Views: 4657

Answers (2)

Flat Cat
Flat Cat

Reputation: 916

2 Things finally fixed it... I ran the update in the query designer and it gave me this weird syntax of parenthesis around the WHERE statement so it looked like:

Where (((dbo_vw_TS_Activity.[id]) = 1124))

Finally, it wanted double-double-quotes for variables, like:

SET [Remarks] = ""The updated remarks...""

Suddenly, it's happy. Consequently, I am too.

Upvotes: 0

Ken White
Ken White

Reputation: 125687

This should work fine, AFAICT, from what you've posted along with your comment above.

CurrentDb.Execute "Update dbo_TS_Activity Set [Remarks] = 'Updated Remarks'   Where [id] = 1124 AND [Emp_Name] = 'CONFUSED'  AND [Approved] = 0"

Upvotes: 0

Related Questions