Ilf and SQL query

How can I write SQL query using IIf function in it? For example, I would like to write something like this:

SELECT priceNetto, vat, 
    PriceBrutto:Ilf(Country="ABC", priceNetto*1.22, priceNetto)

but it won't compile.

PriceBrutto is a dynamic column开发者_运维问答 (there is no that column in query, but I would like to "build" this column programmatically like in my example).

You can use it like this:

SELECT priceNetto, vat, IIf(Country='ABC', priceNetto*1.22, priceNetto) AS PriceBrutto, ...

Try:

SELECT priceNetto, vat, IIF(Country="ABC", priceNetto*1.22, priceNetto) as PriceBrutto ...

A variation on the theme, a little easier to follow to my eye:

SELECT priceNetto, vat, 
       priceNetto * IIf(Country='ABC', 1.22, 1) AS PriceBrutto, 
       ...

Note that the data type of the expression's result will change depending on the value of Country, which is a little odd. If Country = 'ABC' then the result is almost certain to be coerced to type DECIMAL. Is this your intention? Possibly yes: sounds like you are applying tax and the Access Database Engine's DECIMAL type exhibits rounding by truncation, same as the tax office; other types exhibit banker's rounding which is likely not the correct rounding algorithm for tax in my experience.

However, if you do not want a DECIMAL result, you will need to explicitly cast either the result or the multipliers to the required type.