CODEWITHSUNDEEP
sqlms-access
user3124181
user3124181

Reputation: 863

SQL Syntax Error Missing Operator MS Access

I'm trying to run a query that will update a field in one table if a field in another table is equal to test. Here is the code:

UPDATE Table1 AS t1 INNER JOIN Table2 AS t2 
ON t1.Field1 = t2.F_Name
SET t1.Field4 =
    (CASE 
        WHEN t2.PlayField = 'test' THEN 'test' 
        ELSE 'No test' 
    END);

However, I always receive a Syntax Error (missing operator) when I run it. Not sure what I'm doing wrong...

Upvotes: 0

Views: 601

Answers (3)

HansUp
HansUp

Reputation: 97131

CASE ... WHEN is not supported in Access SQL. Use IIf instead.

UPDATE Table1 AS t1 INNER JOIN Table2 AS t2 
ON t1.Field1 = t2.F_Name
SET t1.Field4 =
    IIf(t2.PlayField = 'test', 'test', 'No test');

Upvotes: 2

Mark C.
Mark C.

Reputation: 6460

Since you only want to understand the issue..Your SQL :

UPDATE Table1 AS t1 INNER JOIN Table2 AS t2 
ON t1.Field1 = t2.F_Name
SET t1.Field4 =
    (CASE 
        WHEN t2.PlayField = 'test' THEN 'test' 
        ELSE 'No test' 
    END);

MS Access doesn't support the CASE statement. It looks like SQL Server, not MS Access.

You could try:

 SET t1.Field4 =
IIf([t2].[playfield]='test','test','No test');

This basically says: Set t1.Field = If t2.PlayField = 'Test', we will use the word 'test', if it doesn't we will use 'No Test'.

Upvotes: 2

Maciej Los
Maciej Los

Reputation: 8591

If the question is related to MS Access database engine...

Have a look here: MS Access 2003: Update Query that updates values in one table with values from another table

Your query should look like:

UPDATE TableName
SET TableName.Field1 = (SELECT AnotherTable.Field2
                        FROM AnotherTable
                       WHERE AnotherTable.Field3 = TableName.Field2)
WHERE (<<condition>>);

CASE WHEN ... END statement is only possible to use within VBA. To achieve such as conditional result within query, use IIF function.

See: IIF function

Upvotes: 0

Related Questions