IFF statement with calcuation

Question

I am trying to calculate fees based on size criteria through SQL statements in MS Access.

For example if`x < 24, calculate Quantity * Fee or if x >= 24 calculate quantity x AltFee

Here is the Iff statement I wrote in Access

IIf([Query Testing].[Size]<"24",[Query Testing].[Quantity]*[DepositAndFees].[Fee],IIf([Query Testing].[Size]>="24",[Query Testing].[Quantity]*[DepositAndFees].[AltFee]))

Answer 1

Is your Size field a Text data type? You have quotes around it, which would evaluate it as a string. Try running it without quotes (Assuming it is a number type)

IIf([Query Testing].[Size]<24,[Query Testing].[Quantity]*[DepositAndFees].[Fee],
IIf([Query Testing].[Size]>=24,[Query Testing].[Quantity]*[DepositAndFees].[AltFee]))

Answer 2

Try removing those double quotes around your values so that they are treated as numbers rather than strings (text):

IIf([Query Testing].[Size]<24,[Query Testing].[Quantity]*[DepositAndFees].[Fee],IIf([Query Testing].[Size]>=24,[Query Testing].[Quantity]*[DepositAndFees].[AltFee]))

Answer 3

There is no need for the second IIf, so it can be reduced to:

[Query Testing].[Quantity]*IIf([Query Testing].[Size]<24,[DepositAndFees].[Fee],[DepositAndFees].[AltFee])