CODEWITHSUNDEEP
ms-access
Dariusz Tarczynski
Dariusz Tarczynski

Reputation: 16731

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).

Upvotes: 1

Views: 498

Answers (3)

onedaywhen
onedaywhen

Reputation: 57063

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.

Upvotes: 0

Jim
Jim

Reputation: 81

Try:

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

Upvotes: 0

Nick Dandoulakis
Nick Dandoulakis

Reputation: 43130

You can use it like this:

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

Upvotes: 5

Related Questions