Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post

    MSAccess 2007 - replace Null with 0

    Hi guys.

    I have a query that displays the total number of records from another query that counts them, and the total number of records that contain 'Yes' in one of the fields from another query. The issue is, if there are no records that contain 'Yes', I get null, when I actually need a '0'.

    I will paste the SQL code below. Currently, when I run the query, it is asking me for a value for 'CountofDB Part Number', and what ever value I type in, shows up in the query.

    Thank you in advance for your help!

    KST

    *********

    SELECT qry_Order_List_BO_Count_of_All.[CountOfDB Part ID], IIf([CountofDB Part Number] Is Null Or [CountofDB Part Number]="",0,[CountofDB Part Number]) AS My_Field
    FROM qry_Order_List_BO_Count_of_All LEFT JOIN qry_Order_List_BO_Count_of_YES ON qry_Order_List_BO_Count_of_All.[Order ID] = qry_Order_List_BO_Count_of_YES.[Order ID];

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Please show the queries that this query is based upon, it would be good to make the DB available so we can see what you are doing.

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post
    Thanks Patt! The query 'qry_Order_List_BO_Count_Display'; I need a '0' when there is no value.
    Attached Files Attached Files

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts
    Does this do the job?
    SELECT qry_Order_List_Count_of_All.[CountOfDB Part ID] AS [Total List Count], qry_Order_List_Count_of_All.[Order ID], nz([CountOfPart Number],0) AS TotalYesCount
    FROM qry_Order_List_Count_of_All LEFT JOIN qry_Order_List_BO_Count_of_YES ON qry_Order_List_Count_of_All.[Order ID] = qry_Order_List_BO_Count_of_YES.[Order ID];

  5. The Following User Says Thank You to patt For This Useful Post:

    KSTidmore (2016-06-20)

  6. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Carrollton, Georgia, USA
    Posts
    123
    Thanks
    3
    Thanked 1 Time in 1 Post
    Heck yes! That does it! Thank you Pat! You've helped me out before! I appreciate the wisdom! Have a great week! -Scott

Posting Permissions

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