# Thread: IIF clause (Access 2000)

1. ## IIF clause (Access 2000)

How can i build an IIF clause in the query depending on the value of a field? The values in the field size taken into account are as follows

1 0.138
4 0.552
18 2.48
20 2.66
60 6.27
180 6.18
205 19

the expression must take place in the field [extendedprice].it means that if the size = 1, then the extendedprice should be [extendedprice]* 0.138 etc etc
I do not want to build a special table for the size. Is it possible to do it with an IIF clause when both fieds are present in once and the same table ?

2. ## Re: IIF clause (Access 2000)

Using a table would be much more flexible - it would be very easy to change, add or remove values. Using an IIf expression is more difficult to maintain.

The IIf expression could look like this:

IIf([Size]=1,0.138,IIf([Size]=4,0.552,IIf([Size]=18,2.48,IIf([Size]=20,2.66,IIf([Size]=60,6.27,IIf([Size]=180,6.18,IIf([Size]=205,19,0)))))))

Note: the value for size = 180 appears to be incorrect. It is lower than the value for size = 60.

3. ## Re: IIF clause (Access 2000)

You can set up an Iif statement as follows. Iif(1, .138, Iif(4, .552, Iif(18, 2.48, ...)))

A better option would be the Switch function - Switch(expr-1, value-1[, expr-2, value-2

4. ## Re: IIF clause (Access 2000)

Thank you so much for your reply. The values for the size are fixed and will no be changed. My condition was that if size = 1 then extendedprice = extendedprice * 0.138. I can do it only once and it works so :

Expr2: IIf([size]=1;[extendedprice]*0,138;[extendedprice]) ;

The above expression is ok and works. But I cannot add the other sizes. I always get errors.For example
Exprx: IIf([size]=1;[extendedprice]*0,138;[extendedprice]) ; IIf([size]=4;[extendedprice]*0,552;[extendedprice]))

Gives me an error. Could you point me where the errors lies ?

5. ## Re: IIF clause (Access 2000)

It seems to me like you are trying to enter multiple IIF commands on the one line.
If you want to use IIF to solve the problem, then use Hans solution.

If you want a clearer looking command use the switch command.

6. ## Re: IIF clause (Access 2000)

You can multiply at the end:

NewPrice: IIf([Size]=1,0.138,IIf([Size]=4,0.552,IIf([Size]=18,2.48,IIf([Size]=20,2.66,IIf([Size]=60,6.27,IIf([Size]=180,6.18,IIf([Size]=205,19,0)))))))*[ExtendedPrice]

or

NewPrice: Switch(Size]=1,0.138,[Size]=4,0.552,[Size]=18,2.48,[Size]=20,2.66,[Size]=60,6.27,[Size]=180,6.18,[Size]=205,19)*[ExtendedPrice]

A special table would still be a lot easier, even if the values never change.

7. ## Re: IIF clause (Access 2000)

Dear Hans

Thank you for your help. You helped me to build my query in this way.For example in a shortened variant :
IIf([Size]=1;0,138;IIf([Size]=4;0,552;0))*[ExtendedPrice]
It works great! But i have forgotten the cases when size is not 1 or 4. Up to now i get nothing in the expression. I want in these cases the newPrice to be = extendedprice. In other words, if size = 1, the extendedprice to be
multiplied by 0,138 but otherwise the extendedprice should remain the same, and not to get 0 in the expression. Can you help me ?

8. ## Re: IIF clause (Access 2000)

Change the last 0 to 1:

IIf([Size]=1;0,138;IIf([Size]=4;0,552;1))*[ExtendedPrice]

9. ## Re: IIF clause (Access 2000)

just to let you know that it works great !

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•