Access syntax

Hi:

 I need to get this syntax right in MS Accesss.

So we lookup first table MAP_TBL on 3 columns to match and they all match then we return the MYFLD in that table.
 

If all 3 columns do not match then we lookup the second table MAP_TBL1 on 2 column match and if they both match then we return the MYFLD in that table.

If there is no match in either then we return "OTHER".


SELECT
 IIF(MAP_TBL.[MYFLD] IS NOT NULL, MAP_TBL.[MYFLD] , (MAP_TBL_1.[MYFLD] IS NOT NULL,MAP_TBL_1.[MYFLD], "OTHER" ) ) AS final_value

froM

MYTABLE1

left join MAP_TBL ON MYTABLE.COL1= MAP_TBL.COL1 AND MYTABLE.COL2= MAP_TBL.COL2 AND MYTABLE.COL3= MAP_TBL.COL3  

left join MAP_TBL1 ON MYTABLE.COL1 = MAP_TBL1.COL1 AND MYTABLE.COL2 = MAP_TBL1.COL2