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

    Exclamation Unanswered: How to get a field to change negative number to 0

    So here is what I have thus far =iff(nz([1-30 days])+nz([31-60 days])-nz([consultfee])>0, 0,(nz([1-30 days])+nz([31-60 days])-nz([consultfee]))

    That statement doesn't work though. It works just fine with out the iff statement portion. I am able to add/subtract across the rows in each field. It's just the iff statement I need help with. The consultfee is the field that sometimes has negative numbers that I want to display as 0.

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it just doesn't work meaning what precisely
    what is the purpose of the NZ function, you don't specify the return vale if null, so you are leaving it upto the SQL engine to interpret waht value it should use.

    what happens if (temporarily you forget the IIF bit and instead just assign
    Code:
    nz([1-30 days])+nz([31-60 days])-nz([consultfee])
    to the control
    build your application using small (proven steps, dont be tempted to go for a big bang and do everything in one hit, as it can be an unecessarily &^)(*&)(**& to debug
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Apr 2015
    Posts
    4

    Exclamation Reply

    Ok That works fine but how do I get the negative numbers in the consultfee field to change to zero? Not all of the numbers in that field are negative I want to be able to keep the positive number positive.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok so you've proved the calculation bit works, all you need do now is revisit your IIF statement, which takes the form of
    IIF(boolean expression, value_if_true, value_if_false )
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Apr 2015
    Posts
    4

    Exclamation Example

    Could you write out what the IIF statement would look like with the example I gave you?

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by paulfields4 View Post
    Could you write out what the IIF statement would look like with the example I gave you?
    No, learn by doing

    This place will help with problems but wont (usually) 'do'. you need to do the detsail work yourself., if for no other reason it makes you think about the problem, try to work out what is going wrong, trying to prove that hypothesis and then develop a fix for the problem.

    By asking what you have in effect you are saying your time is far, far too important to get your hands dirty, and someone else must rally round and provide a 'gimme'. If nothing else, so far this has consumed at least 7 hours of elapsed time. there is nothing especially complex about this problem. make an effor tot understand what your code is doing, try to work out what is going wrong, then develop a fix to correct that problem. that fix may require a different coding approach, it may not

    Although the NZ function has a default retuirn value, I'd stroingly advise that, if for good coding practise, alwasy specify the value you want even if a default is available

    ferinstance:-
    nz(aintegervalue, 0)
    nz(adecimalvalue, 1.2345)
    nz(astringvalue,'blah')
    nx(adatevalue,date())

    FWIW I think you have mixed up your boolean test or conversely mixed up the true / false values.
    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
  •