IIF Microsoft Access Statement Error Help -在一个查询中出现多个IIF语句.&

Question

希望有人能帮助我。

我试图运行这个查询，但我得到下面的错误。我需要根据不同表中的不同字段构建逻辑，然后将该数据写入表中的新字段。这只是我的查询的开始，但我已经得到错误:/

我要写的逻辑是

如果Containersize = '20'和CBMRatePLX <20MRate然后使用CBMRatePLX如果Containersize = '40'和CBMRatePLX <40 mrate然后使用CBMRatePLX，如果Containersize = 'LCL'，则使用40MRateCBMRatePLX & lt;LCLMRate使用CBMRatePLX else LCLMRate

这是我写的查询，但我有一些语法错误。任何帮助都会很感激。:)

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]))
From [CustomerRates] , [Shipments]
Where
[shipments]![customerid]=[customerRates]![customerid]

Answer 1

考虑:

IIf([ContainerSize] = 20, IIf([CBMRate] * [PLX] < [20MRate], [CBMRate] * [PLX], [20MRate]), 
IIf([ContainerSize] = 40, IIf([CBMRate] * [PLX] < [40MRate], [CBMRate] * [PLX], [40MRate]),
IIf([CBMRate] * [PLX] < [LCLMRate]), [CBMRate] * [PLX], [LCLMRate])))

或

Switch([ContainerSize] = 20, IIf([CBMRate] * [PLX] < [20MRate], [CBMRate] * [PLX], [20MRate]),
[ContainerSize] = 40, IIf([CBMRate] * [PLX] < [40MRate], [CBMRate] * [PLX], [40MRate]),
True, IIf([CBMRate] * [PLX] < [LCLMRate]), [CBMRate] * [PLX], [LCLMRate]))