Trying to get this iif function to work but keep getting wrong results ie minus or 1st part result instead of second part when applicable.
There are 2 charge levels based on a consignment weight minus an allowed value
Charge 1 is if a consignmet is greater than 5 kilo then [Weight]-5 gives me the additionl kilos weight - OK
but Charge 2 if the consignment is greater than 21 kilos it falls into a different charge methord and I need to take the [Weight] - 21 = extra kilos
I have the below in a query which wirks on the first part 100% but the results are always [Weight]-21
ExtraKilos: IIf([Weight]>5<21,([Weight]-5),IIf([Weight]>21,([Weight]-21),0))
[Weight] Values 1to 5 are correct at 0 but > 5 to whatever will always minus the 5
any ideas appricated
:banghead:
There are 2 charge levels based on a consignment weight minus an allowed value
Charge 1 is if a consignmet is greater than 5 kilo then [Weight]-5 gives me the additionl kilos weight - OK
but Charge 2 if the consignment is greater than 21 kilos it falls into a different charge methord and I need to take the [Weight] - 21 = extra kilos
I have the below in a query which wirks on the first part 100% but the results are always [Weight]-21
ExtraKilos: IIf([Weight]>5<21,([Weight]-5),IIf([Weight]>21,([Weight]-21),0))
[Weight] Values 1to 5 are correct at 0 but > 5 to whatever will always minus the 5
any ideas appricated
:banghead: