VBA Update Statement - Missing Operator in Query Expression

Question

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:

Single quotes
Double quotes
Double single (two apostrophes) quotes
No quotes
Opening the linked table and manually editing it (it works)
Crying (just kidding)

Your context is a little unclear. Are Updated Remarks and CONFUSED literal text, or are they variables you're trying to concatenate into the SQL? — Ken White
– Ken White, Commented Apr 8, 2012 at 16:38
Those are just strings I'm trying to insert. "Updated Remarks" is the string that is being updated. "Confused" is the employee name that is being updated. — Flat Cat
– Flat Cat, Commented Apr 8, 2012 at 18:21

Ken White · Accepted Answer · 2012-04-08 18:27:47Z

0

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"

answered Apr 8, 2012 at 18:27

Ken White

126k15 gold badges237 silver badges476 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Flat Cat Over a year ago

Then what's Access getting all worked up over? :) It still won't work as VBA code.

Ken White Over a year ago

@Ben: As I said, with what you posted it should work. It's hard to tell why it doesn't when you didn't post your actual query. (I doubt you're really trying to set Remarks to Updated Remarks for an employee named CONFUSED.)

Flat Cat Over a year ago

Well, it was more complicated than that so I reduced it down to what my example was. Even that simple statement fails. That is the actual statement that fails. It fails with variables or hard-coded values.

Flat Cat · Accepted Answer · 2012-04-09 02:42:50Z

0

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.

answered Apr 9, 2012 at 2:42

Flat Cat

9164 gold badges13 silver badges24 bronze badges

1 Comment

phoog Over a year ago

The parentheses in the first example are not necessary. The query designer inserts them because they might have been necessary if the expression had been more complex. The significant factor is the use of double quote characters rather than single quote characters. To indicate a double-quote character in a VBA string, you need to double it, hence the need for double-double quotes in your string literal. The string actually contains single double-quote characters. For example, the string """" is one character long.

Collectives™ on Stack Overflow

VBA Update Statement - Missing Operator in Query Expression

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related