I would like my SQL to return a Zero if a Null is returned

Posted on 2011-04-25
Last Modified: 2012-05-11

I am using the following expression in an Access query to generate a proportion defective

(QNs_LV_FV_qry.[SumOfDefectQty (ext)]/Shipments_FV_qry.SumOfDelivery_Qty)*1000000 AS QN_PPM

Sometimes, however, there are no defects, so the above expression returns a null. I would like it to return a zero in such cases.

Question by:Rex85
    LVL 40

    Accepted Solution

    You can try NZ function.

    NZ((QNs_LV_FV_qry.[SumOfDefectQty (ext)]/Shipments_FV_qry.SumOfDelivery_Qty)*1000000,0) AS QN_PPM

    Author Closing Comment


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Better Security Awareness With Threat Intelligence

    See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

    In database programming, custom sort order seems to be necessary quite often, at least in my experience and time here at EE. Within the realm of custom sorting is the sorting of numbers and text independently (i.e., treating the numbers as number…
    Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
    Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    794 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now