Access SQL syntax error (missing operator) when query contains multiple JOINs

Question

I'm trying to make an UPDATE query (using Access 2013) that calculates a value based on values stored in two separate linked tables. Here is the code I'm using:

UPDATE tblCreatures 

INNER JOIN tblRole ON tblCreatures.Role = tblRole.RoleName 
INNER JOIN tblRank ON tblCreatures.Rank = tblRank.RankName

SET tblCreatures.HP = ((tblRole.Level_0_HP + (tblCreatures.NominalLevel * tblRole.BonusHP)) * tblRank.HP_Multiplier);

This gives me a syntax error, saying

Syntax error (missing operator) in query expression "tblCreatures.Role = tblRole.RoleName INNER JOIN tblRank ON tblCreatures.Rank = tblRank.RankNam"

(and yes, it cuts off at RankNam, not RankName)

Testing things out, if I remove one of the inner joins (and thus all references to that table) then the update query works just fine, but if I put the other inner join back in, I continuously get this same syntax error. I don't understand why... I should be able to put two inner joins next to each other, shouldn't I?

Again, this update works perfectly fine if I take out the second inner join. It's only when I use both inner joins that I get an error. — Richard Winters
– Richard Winters, Commented Jan 10, 2015 at 13:09

Gord Thompson · Accepted Answer · 2015-01-10 13:16:16Z

2

Access SQL requires parentheses when a query contains multiple JOINs. If you build the query in Access' query designer it will look like this (reformatted for clarity):

UPDATE 
    (
        tblCreatures 
        INNER JOIN 
        tblRole 
        ON tblCreatures.Role = tblRole.RoleName
    ) 
    INNER JOIN 
    tblRank 
    ON tblCreatures.Rank = tblRank.RankName 
SET tblCreatures.HP = ((tblRole.Level_0_HP + (tblCreatures.NominalLevel * tblRole.BonusHP)) * tblRank.HP_Multiplier);

answered Jan 10, 2015 at 13:16

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

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

1 Comment

Richard Winters Over a year ago

Thank you! I even tried parenthesis, but I didn't include tblCreatures inside the parenthesis. I wonder why it requires that... but regardless, this worked like a charm. Thanks for illuminating a weird quirk of Access's syntax for me!

Collectives™ on Stack Overflow

Access SQL syntax error (missing operator) when query contains multiple JOINs

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related