SRATNA
SRATNA

Reputation: 21

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?

Upvotes: 0

Views: 3938

Answers (2)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112289

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

Upvotes: 0

Reserve4Todd
Reserve4Todd

Reputation: 19

I don't think that you can mix number/text answers in a calculated field. You'd be better served to use a query in this case and not a calculated field. I've avoided using calculated fields thus far do to their limitations (such as this).

Upvotes: 0

Related Questions