Results 1 to 5 of 5
  1. #1
    Join Date
    May 2004
    Posts
    159

    Unanswered: how do I change a null to 0 in a query?

    I have a query that picks up and sums values in a field to be later subtracted
    from another number. If the field is empty because data has not been entered in it yet I want the query to substitute a 0. However if I put an expression for that in the criteria then it looks for that case which is not what I want.
    If I don't have a 0 there then the operation does not subtract properly on the next calculation so I would like to fix this.
    I tried doing this in code on the report but it would not work there. I need to have the nulls changed to 0 before it gets to the report.

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Hi,

    Select NZ(column, 0)
    from tableA

    Thank You.
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    May 2004
    Posts
    159
    >Hi,

    Select NZ(column, 0)
    from tableA
    ===================================
    Thanks for the quick response. I suspected it would have to be in the SQL statement but I am not sure how to insert the Select NZ statement

    Here is my SQL-- I want the table pwp700tc_c_po_detail and field "invoiced_amount" to be 0 if null Thanks!

    SELECT QRYWMHIINVSUB.PONUM, PWP700TC_C_PO_DETAIL.PURCHASE_ORDER_NUM, QRYWMHIINVSUB.TotalAmount, Sum(PWP700TC_C_PO_DETAIL.INVOICED_AMT) AS SumOfINVOICED_AMT, [TotalAmount]-[invoiced_amt] AS BALANCE, QRYWMHIINVSUB.PONumber, QRYWMHIINVSUB.PODate
    FROM QRYWMHIINVSUB LEFT JOIN PWP700TC_C_PO_DETAIL ON QRYWMHIINVSUB.PONUM = PWP700TC_C_PO_DETAIL.PURCHASE_ORDER_NUM
    GROUP BY QRYWMHIINVSUB.PONUM, PWP700TC_C_PO_DETAIL.PURCHASE_ORDER_NUM, QRYWMHIINVSUB.TotalAmount, [TotalAmount]-[invoiced_amt], QRYWMHIINVSUB.PONumber, QRYWMHIINVSUB.PODate;

  4. #4
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    SELECT QRYWMHIINVSUB.PONUM, PWP700TC_C_PO_DETAIL.PURCHASE_ORDER_NUM, QRYWMHIINVSUB.TotalAmount, Sum(NZ(PWP700TC_C_PO_DETAIL.INVOICED_AMT, 0)) AS SumOfINVOICED_AMT, [TotalAmount]-[invoiced_amt] AS BALANCE, QRYWMHIINVSUB.PONumber, QRYWMHIINVSUB.PODate
    FROM QRYWMHIINVSUB LEFT JOIN PWP700TC_C_PO_DETAIL ON QRYWMHIINVSUB.PONUM = PWP700TC_C_PO_DETAIL.PURCHASE_ORDER_NUM
    GROUP BY QRYWMHIINVSUB.PONUM, PWP700TC_C_PO_DETAIL.PURCHASE_ORDER_NUM, QRYWMHIINVSUB.TotalAmount, [TotalAmount]-[invoiced_amt], QRYWMHIINVSUB.PONumber, QRYWMHIINVSUB.PODate;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  5. #5
    Join Date
    May 2004
    Posts
    159
    Thanks! now I have an idea on how to use the nz function.
    I did also have to change [totalamount]-[invoiced_amt] as Balance
    to [total_amount]-[sumofinvoiced_amount] as Balance
    to be able to pick up that inserted 0 properly.

    Quote Originally Posted by r123456
    SELECT QRYWMHIINVSUB.PONUM, PWP700TC_C_PO_DETAIL.PURCHASE_ORDER_NUM, QRYWMHIINVSUB.TotalAmount, Sum(NZ(PWP700TC_C_PO_DETAIL.INVOICED_AMT, 0)) AS SumOfINVOICED_AMT, [TotalAmount]-[invoiced_amt] AS BALANCE, QRYWMHIINVSUB.PONumber, QRYWMHIINVSUB.PODate
    FROM QRYWMHIINVSUB LEFT JOIN PWP700TC_C_PO_DETAIL ON QRYWMHIINVSUB.PONUM = PWP700TC_C_PO_DETAIL.PURCHASE_ORDER_NUM
    GROUP BY QRYWMHIINVSUB.PONUM, PWP700TC_C_PO_DETAIL.PURCHASE_ORDER_NUM, QRYWMHIINVSUB.TotalAmount, [TotalAmount]-[invoiced_amt], QRYWMHIINVSUB.PONumber, QRYWMHIINVSUB.PODate;

Posting Permissions

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