update query question

[longhair]

afternoon all,
sorry, but my sql is not up to par at the moment so i was wondering if someone could take a look at the below and offer some advice.

UPDATE tbl1 INNER JOIN (tbl2 INNER JOIN tbl3 ON tbl2.[field1] = tbl3.[field1]) ON tbl1.field2 = tbl2.[field2] SET tbl1.field3 = IIf(tbl3.[field4]='a',tbl3!field5)
WHERE (((tbl2.[field2])=[tbl1].[field2]) AND ((tbl3.field5)>0) AND ((tbl3.[field6])='X') AND ((tbl2.[field6])='X') AND ((tbl3.field7)='Y') AND ((tbl3.[field4])='a' Or (tbl3.[field4])='b'));

the sql above is modified from actual code but should give you an idea.
basically tbl1 has a few fields that need to be updated from data in tbl3. i need tbl2 to get to tbl3. i have a simple select query which returns the records that i need from tbl3 but am now trying to convert that into an update query. just to let you know tbl3 can contain up to 2 records for each entry that needs to be updated in tbl1. data from these 2 records will be entered into different fields in tbl1.
i think i'm missing something simple here but i just don't see it.
thanks in advance.
regards,
longhair

 

[longhair]

forgot to add that the error i am receiving is
'operation must use an updateable query'.
regards,
longhair

 

[genomon]

Bad syntax. Should be UPDATE...INNER JOIN...ON....
SET....WHERE...

"Don't be irreplaceable. If you can't be replaced, you can't be promoted."

 

[randy700]

SET tbl1.field3 = IIf(tbl3.[field4]='a',tbl3!field5

You have no ELSE clause in your iif statement.
Perhaps something like...

SET tbl1.field3 = IIf(tbl3.[field4]='a',tbl3!field5[COLOR=red][b], tbl3!field6[/b][/color])

Randy