Results 1 to 9 of 9
  1. #1
    jo15765's Avatar
    jo15765 is offline Expert
    Windows XP Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    672

    Addition With 0's


    I have been working all afternoon to try to get my query to return a 0 when there is no results to return, I have used the NZ function as well as the IIF function but nothing is causing the query to return 0 when it is 0. Can someone help!
    Code:
    IIF([tableStore1Info].[NumSold] IS NULL,0,[tableStore1Info].[NumSold])+IIF([tableStore2Info].[NumSold] IS NULL,0,[tableStore2Info].[NumSold])
    NZ([tableStore1Info].[NumSold],0) + NZ([tableStore2Info].[NumSold],0)

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    IS NULL is not correct in expression, use IsNull([fieldname])

    Shouldn't this be enough:

    Nz([tableStore1Info].[NumSold],0) + Nz([tableStore2Info].[NumSold],0)

    What do you mean by 'no results' to return? If no records meet criteria then nothing will show no matter how you calculate that expression.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    jo15765's Avatar
    jo15765 is offline Expert
    Windows XP Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    672
    I will test the NZ code you posted above when i can get back to my PC.

    I meant 'no results' as I am running several of these calculations in one query, and I wasn't sure if it was one result not returning any resultscausing the entire query to bomb out.
    Like NumSold is the above code, well I may have bonusSold which the code would be
    Nz([tableStore1Info].[bSold],0) + Nz([tableStore2Info].[bSold],0)

    So NumSold for example could return 10 but bonus sold could return 0. SInce I am running them in the same query, could one calculation returning 0 cause the entire query to not return any results?



  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    No, the calculations will not cause that. Filtering can cause query not to return results if no records meet criteria.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    jo15765's Avatar
    jo15765 is offline Expert
    Windows XP Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    672
    Hmm...then I will give the NZ() code you posted below a twirl and see if that is what is causing the calculation to not display properly.

  6. #6
    jo15765's Avatar
    jo15765 is offline Expert
    Windows XP Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    672
    I am still getting the same issue even with the NZ function. I am attaching a sample db with 1 query showing the issue of incorrect calculations, and 2 tables, that the calculation is performed on.

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    No records show for two reasons:

    1. there is no link (JOIN clause) between the two tables

    2. tbl1 has no records

    Why do you have two tables with identical structure?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    jo15765's Avatar
    jo15765 is offline Expert
    Windows XP Access 2000
    Join Date
    Nov 2010
    Location
    6 Feet Under
    Posts
    672
    What field would I join on --- I may be missing it but I don't see the relationship between the data to create a join in this case.

    The two tables have identical structures as they are from 2 separate locations and we haven't yet merged databases into one single table.

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,633
    Could be what you really need is UNION.

    Review https://www.accessforums.net/queries...ort-36311.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. quary addition subtraction
    By agyapong isaac in forum Queries
    Replies: 2
    Last Post: 12-28-2012, 12:07 PM
  2. parsing for addition
    By imintrouble in forum Reports
    Replies: 6
    Last Post: 10-27-2011, 03:00 PM
  3. Combo box-Selective addition
    By reidn in forum Forms
    Replies: 1
    Last Post: 07-20-2011, 11:44 AM
  4. Getting #Number ! on addition of 5 textbox
    By Grooz13 in forum Forms
    Replies: 0
    Last Post: 08-18-2010, 07:49 AM
  5. addition with 0
    By Peljo in forum Access
    Replies: 0
    Last Post: 02-28-2008, 08:12 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums