Ilf and SQL querry

Question

Hello. How to write SQL querry using Ilf function in it? For exaple i would like to write some like this: SELECT priceNetto, vat, PriceBrutto:Ilf(Country="ABC", priceNetto*1.22, priceNetto)

but it wont compile. PriceBrutto is a dynamic column (there is no that column in querry, but im wolud like to "build" this column programically like in my example).

Answer 1

You can use it like this:

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

Answer 2

Try:

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

Answer 3

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.