Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2015
    Posts
    4

    Exclamation Unanswered: Trying to do IIF statement with two critieria

    The statement is as follows
    =IIf(Nz([1-30 Days]) And Nz([31-60 Days days]) And Nz([61-90 Days days]) And Nz([91-180Days days]) And Nz([180-360Day days]) And Nz([Over360Day days])>=0,0 Or IIf(Nz([1-30 Days]) And Nz([31-60 Days days]) And Nz([61-90 Days days]) And Nz([91-180Days days]) And Nz([180-360Day days]) And Nz([Over360Day days])<=0,([ConsultFee])))

    Basically what I am trying to do is if there is a negative number or a zero in the following fields the consultant fee will come back as zero. If there is a number greater than zero the consultantfee will come back with the fee for that invoice.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what is [1-30 days]
    what are you comparing it to to get your boolean evaluation

    do you mean
    IIf(Nz([1-30 Days]) <= 0 OR Nz([31-60 Days days]) <= 0...,0, [ConsultFee])
    Im guessing you want to display the consultfeee if there is a positive number on anyone of those values [1-30 days],......
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    If your IIF statements get THAT complicated, then make a Function.
    Then the query field would be: calcDays([day])

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by ranman256 View Post
    If your IIF statements get THAT complicated, then make a Function.
    Then the query field would be: calcDays([day])
    +1
    good point, and well made
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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