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

    Error in the IIF Clause (Access 2000)

    I have an error in my IIF Clause where i calculate either cartons or quantity.The calculations are based on the presence of cartons or of quantity.When i have a full carton, that is when i have both cartons and quantity, the calculation is all right, but when cartons are not present, then the calculation fails.Can you help me correct my IIF clause ? I need to fill in the fields NetPrice and Surcharge

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error in the IIF Clause (Access 2000)

    Does this expression for NetPrice do what you want?
    <code>
    IIf(Nz([cartons],0)>0,[ddp]*[cartons]*[size]*[pack],[ddp]*[size]*[quantity])
    </code>
    Can you explain how you want to calculate the surcharge if there is no value for cartons?

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

    Re: Error in the IIF Clause (Access 2000)

    Thank you Hans ! The expression for the NetPrice works simply great ! For the surcharge,if there is no value for the cartons the calculation should be done on the basis of the quantity. When a carton is not full, there is only a quantity. Maybe this is the source of the error ?

    kind regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error in the IIF Clause (Access 2000)

    Does it do what you want if you replace [cartons] with Nz([cartons],[quantity]) in the expression?

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

    Re: Error in the IIF Clause (Access 2000)

    Yes it does and now i have all the fileds filled. Thank you very much. It works great !

    I wish you a great weekend

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

    Re: Error in the IIF Clause (Access 2000)

    Dear Hans. As you rughtly suggested i may have problems when the cartons are 0 and the calculations hsould be carried out only with the quantity. I am sending you the attachement with this case.I have also put the NZ condition. Will you have a look ? The surcharge is 0 when i have no cartons/

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Error in the IIF Clause (Access 2000)

    Try this:

    surcharge: IIf([Size]=1,0.138,IIf([Size]=0.4,0.138,IIf([Size]=0.5,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)))))))))*IIf(NZ([cartons],0)=0,[quantity],[cartons])

    (It is probably wrapped to two or more lines on your screen, but it is actually a single expression)

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

    Re: Error in the IIF Clause (Access 2000)

    Thank you. it is OK ! I think i have finished, well entirely with you help, my task.By the way it was very important to me. I am very very grateful.
    I wish you a nice weekend

Posting Permissions

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