Solved Calculations don't work if one of the fields used in calculation is a null (1 Viewer)

[talha]

Hi,

I am using the following formula.

Select a, b, c, d, (a+b)/c) as perc
from
table x
group by a, b, c, d


The above works when I have a value in both a and b, but if either field a or field b is null, then the formula returns nothing...

I've attached the sample file

Please advise how to fix it.

Thanks

 

[The_Doc_Man]

You must understand that when a null becomes involved in something, all computations go out the window. The null is propagated throughout that computation and pretty much takes it over. When that happens, Access and SQL are actually doing what they are supposed to do - return NULL when you gave it a NULL to work with. The only way around this result is to prevent a null from ever entering the picture.

At least two different approaches could be used. Either

(a) don't allow nulls to be considered at all, perhaps by having a WHERE clause in your SQL, with a "NOT ( x IS NULL )" as a condition... or

(b) use the NZ function to on-the-fly intercept nulls and provide a "default" value when you trip over a null.

There is also an ISNULL( x ) function that returns TRUE if x is NULL, so could be used in the WHERE clause. Note that you CANNOT make a WHERE clause with ".... WHERE X = NULL ..." since NULL is NEVER equal to anything else - including another NULL. The same problem that messes with your computation in a query messes with any comparisons, and for the same reason.

SQL NULL Values - IS NULL and IS NOT NULL

W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more.

www.w3schools.com

Nz Function - Microsoft Support

Syntax: Nz(variant [, valueifnull ] )

support.microsoft.com

Watch out how you approach this since different SQL engines have different vendor-specific functions that you might find on-line if you search for info about null handling.

 

[arnelgp]

you can use:

Nz((a+b)/c), 0), or

Val((a+b)/c) & "")

in your formula, i used the latter on your SQL.

 

[Pat Hartman]

Rather than applying Nz() to the result of the formula, it should be applied to the individual parts. And since divide by zero raises an error so you have to handle that BEFORE the division operation and that requires an IIf()

IIf(Nz(c,0) = 0, 0,(Nz(a,0) + Nz(b,0))/c)