Search code examples
ms-accesscalculated-fieldiif

Calculated Field (IIF)

Okay, well I'm currently designing a database for my university coursework. I am struggling on a calculated field within a query. The calculated field is made up of an IIF expression via the expression builder. The main problem is, the calculated field works, however, as I have put "Not Applicable" within the false part of the IIF, it still comes up as #Error? Here below is my IIF! 

IIf([Quantity]=20,[Total After Discount]*0.95,
    IIf([Quantity]=50,[Total After Discount]*0.925,
        IIf([Quantity]=100,[Total After Discount]*0.9,
            IIf([Quantity]>=200,[Total After Discount]*0.875,"Not Applicable."))))

Any help/ideas or suggestions please?

Solution

  • This query calculates the total AFTER discount. Therefore its input should be the total BEFORE discount. You are probably doing something like this:

    SELECT IIf([Quantity]=20,[Total After Discount]*0.95,...) AS [Total After Discount]
FROM ...

    So in this expression [Total After Discount] is referring to itself, which is not possible. Change it to

    SELECT IIf([Quantity]=20,[Total Before Discount]*0.95,...) AS [Total After Discount]
FROM ...

    If [Total After Discount] is a column in another query or table and you want to use the same column name in the result again, you can do it by qualifying the column name by the table or query name:

    SELECT
    IIf([Quantity]=20, MyTable.[Total After Discount]*0.95, ...)
        AS [Total After Discount]
FROM MyTable

    This eliminates the unallowed recursion.

    But is your logic really correct? Shouldn't it be:

    IIf([Quantity]>=200,[Total Before Discount]*0.875,
    IIf([Quantity]>=100,[Total Before Discount]*0.9,
        IIf([Quantity]>=50,[Total Before Discount]*0.925,
            IIf([Quantity]>=20,[Total Before Discount]*0.95,[Total Before Discount]))))

    Turn the logic around. You are not showing the disount, but the total after discount, so you should display the total instead of "Not Applicable".

    If you were showing the discount then yes, display "Not Applicable":

    IIf([Quantity]>=200,"12.5%",
    IIf([Quantity]>=100,"10%",
        IIf([Quantity]>=50,"7.5%",
            IIf([Quantity]>=20,"5%","Not Applicable")))) As DiscountPercent