Results 1 to 2 of 2
  1. #1
    greidy78 is offline Novice
    Windows 10 Access 2016
    Join Date
    Apr 2019
    Posts
    1

    How to return a Zero instead of null in query

    I have a query that is totaling Cut Lengths of a given cable reel then I use it deduct from the original received qty. The problem is if no cuts were made it omits the record completely. I need the sum of cuts to be zero if no records are found.
    Any help would be greatly appreciated. I have tried NZ and a few others but given my lack of knowledge I believe the root of my problems is in the Select statement..




    SQL is
    SELECT DISTINCTROW [CABLE REELS].[MASTER PART ID], CUTS.[MASTER REEL ID], Sum(CUTS.[CUT LENGTH]) AS [Sum Of CUT LENGTH], [CABLE REELS].[RECIEVED QTY], [CABLE REELS].[VENDOR PART], [CABLE REELS].[RECIEVED COST CAD /FT]
    FROM [CABLE REELS] INNER JOIN CUTS ON [CABLE REELS].ID = CUTS.[MASTER REEL ID]
    GROUP BY [CABLE REELS].[MASTER PART ID], CUTS.[MASTER REEL ID], [CABLE REELS].[RECIEVED QTY], [CABLE REELS].[VENDOR PART], [CABLE REELS].[RECIEVED COST CAD /FT];

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    Advise not to use spaces nor punctuation/special characters (underscore only exception) in naming convention. Also, PascalCase is easier to read than ALLCAPS.

    Don't use INNER JOIN, use LEFT or RIGHT.
    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. How to get a query to return results for null/0 value?
    By NikoTheBowHunter in forum Queries
    Replies: 8
    Last Post: 08-28-2018, 09:04 AM
  2. Query to return blank or null items.
    By mulefeathers in forum Queries
    Replies: 3
    Last Post: 10-24-2013, 12:45 PM
  3. Union Query to return Null
    By dr4ke in forum Queries
    Replies: 8
    Last Post: 07-06-2012, 12:18 AM
  4. Count query return zero instead of null/blank
    By dhicks19 in forum Queries
    Replies: 1
    Last Post: 04-19-2012, 07:52 AM
  5. Query return 0 for null values
    By rachello89 in forum Access
    Replies: 4
    Last Post: 02-23-2012, 08:38 AM

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