Microsoft Access查询多嵌套IIF语句语法错误求助

问题错误原因 #

• 核心是IIF函数语法使用错误：Access的IIF函数要求固定传入3个参数，格式为IIF(判断条件, 条件成立返回值, 条件不成立返回值)，原有代码存在大量括号提前闭合、参数位置拆分、嵌套逻辑层级混乱的问题
• 多表关联逻辑不规范，仅用WHERE匹配关联ID可读性差，推荐用JOIN显式声明关联关系
• 计算字段未指定别名，无法直接识别输出字段名

修正后的查询代码（仅查看计算结果） #

SELECT 
  IIF(
    [Shipments]![ContainerSize] = '20',
    IIF([CustomerRates]![CBMRate] * [Shipments]![PLX] < [CustomerRates]![20MRate], [CustomerRates]![CBMRate] * [Shipments]![PLX], [CustomerRates]![20MRate]),
    IIF(
      [Shipments]![ContainerSize] = '40',
      IIF([CustomerRates]![CBMRate] * [Shipments]![PLX] < [CustomerRates]![40MRate], [CustomerRates]![CBMRate] * [Shipments]![PLX], [CustomerRates]![40MRate]),
      IIF([CustomerRates]![CBMRate] * [Shipments]![PLX] < [CustomerRates]![LCLMRate], [CustomerRates]![CBMRate] * [Shipments]![PLX], [CustomerRates]![LCLMRate])
    )
  ) AS 计算后费率
FROM [CustomerRates] INNER JOIN [Shipments] ON [Shipments]![customerid] = [CustomerRates]![customerid]

若需要将计算结果写入表的新字段（需提前在对应表建好存储结果的字段，示例中字段名为FinalRate存于Shipments表） #

UPDATE [CustomerRates] INNER JOIN [Shipments] ON [Shipments]![customerid] = [CustomerRates]![customerid]
SET [Shipments]![FinalRate] = IIF(
    [Shipments]![ContainerSize] = '20',
    IIF([CustomerRates]![CBMRate] * [Shipments]![PLX] < [CustomerRates]![20MRate], [CustomerRates]![CBMRate] * [Shipments]![PLX], [CustomerRates]![20MRate]),
    IIF(
      [Shipments]![ContainerSize] = '40',
      IIF([CustomerRates]![CBMRate] * [Shipments]![PLX] < [CustomerRates]![40MRate], [CustomerRates]![CBMRate] * [Shipments]![PLX], [CustomerRates]![40MRate]),
      IIF([CustomerRates]![CBMRate] * [Shipments]![PLX] < [CustomerRates]![LCLMRate], [CustomerRates]![CBMRate] * [Shipments]![PLX], [CustomerRates]![LCLMRate])
    )
  )

内容的提问来源于stack exchange，提问作者xAk47x