Results 1 to 10 of 10
  1. #1
    Join Date
    Nov 2008
    Posts
    26

    Unanswered: Returns blank instead of 0

    Here is my current iif statement:

    Code:
    CC Cost: Sum(IIf([Operation]=854,IIf([DStamp]<>#1/26/1986#,[Cost],0)))
    It returns the [Cost] field amount correctly but if data doesn't match the criteria, it will return a blank. What can I change so that if no Cost is applicable, then a 0 value will be returned in the query?

  2. #2
    Join Date
    May 2009
    Posts
    258
    Use the Nz function:
    Code:
    Sum(IIf([Operation]=854,IIf([DStamp]<>#1/26/1986#,Nz([Cost],0),0)))
    You should probably further condense it to one IIf expression:
    Code:
    Sum(IIf([Operation]=854 And [DStamp]<>#1/26/1986#,Nz([Cost],0),0))
    Regards,

    Ax

  3. #3
    Join Date
    Nov 2008
    Posts
    26
    Quote Originally Posted by Ax238
    Use the Nz function:
    Code:
    Sum(IIf([Operation]=854,IIf([DStamp]<>#1/26/1986#,Nz([Cost],0),0)))
    You should probably further condense it to one IIf expression:
    Code:
    Sum(IIf([Operation]=854 And [DStamp]<>#1/26/1986#,Nz([Cost],0),0))
    Regards,

    Ax
    It still returns a blank. There is a good chance that for a particular record, 854 won't exist at all. It could go from 850 to 857 more likely. Is that fact it wouldn't find that value causing it to return the blank?
    ---------------
    NM...I had updated the statement wrong. Your solution works perfectly. Thanks
    Last edited by reeser; 08-31-09 at 14:27.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You can't use And like that in an expression Ax.

    CCCost: Sum(IIf([Operation]=854,IIf([DStamp]<>#1/26/1986#,[Cost],0),0))

    And just to be sure:

    CCCost: Nz(Sum(IIf([Operation]=854,IIf([DStamp]<>#1/26/1986#,[Cost],0),0)),0)
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2009
    Posts
    258
    Really? Hmmm, well I've been using it like that for quite a few years and it seems to work just fine. You're telling me that a boolean operator is not valid in a boolean expression?

    BTW:
    Quote Originally Posted by reeser
    NM...I had updated the statement wrong. Your solution works perfectly. Thanks

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Interesting... my current understanding has been that you can't use such things in the IIf function. Of course it works on many other things, but yeah, I am still under the impression that it doesn't work in an IIf. Must try it out next time I am coding

    Thanks
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    May 2009
    Posts
    258
    Please do let me know what you find out.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Tested. You are correct! Thank you!!!! You've made my day!

    I have been avoiding using AND in such expressions for years, for reasons I no longer remember! No mind as those reasons were obviously not valid!

    This should make things easier ^^

    Thanks again Ax
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    May 2009
    Posts
    258
    Great! Glad I was able to help you realize something so subtle, yet so beneficial.

  10. #10
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Yes, this will save me some frustrations with doing nested IIFs instead of just a simple ANDing. I wonder what made me think that it wasn't possible?! ^^
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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