Results 1 to 9 of 9
  1. #1
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    Lounger
    Join Date
    Nov 2006
    Location
    Denver, Colorado, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #4
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #5
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    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. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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]

    (For your country, use , instead of . and ; instead of , )

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

  7. #7
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: IIF clause (Access 2000)

    Change the last 0 to 1:

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

  9. #9
    3 Star Lounger
    Join Date
    Feb 2004
    Location
    Burgas, Bulgaria
    Posts
    209
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •