Error in adding the null value in query

[confuse]

Hi!! I've search this forum about my problem and theres a lot which are related to this, but i couldnt solve mine, if i apply there solution.

I have query with code as follows:


SELECT Products.ProductID, Products.ProductName, [Order Details].Quantity AS [Order Details_Quantity], [Order Details Petshop].Quantity AS [Order Details Petshop_Quantity], (Nz(Nz([Order Details].Quantity,0)+Nz([Order Details Petshop].Quantity,0))) AS ProductSold, [Inventory Transactions].UnitsReceived, (Nz(nz([UnitsReceived],0)-(Nz([Order Details].Quantity,0)+Nz([Order Details Petshop].Quantity,0)))) AS RemainingProducts
FROM ((Products INNER JOIN [Inventory Transactions] ON Products.ProductID = [Inventory Transactions].ProductID) INNER JOIN [Order Details] ON Products.ProductID = [Order Details].ProductID) INNER JOIN [Order Details Petshop] ON Products.ProductID = [Order Details Petshop].ProductID
GROUP BY Products.ProductID, Products.ProductName, [Order Details].Quantity, [Order Details Petshop].Quantity, [Inventory Transactions].UnitsReceived;


The calculation wont work if one of the value is null, but then i used the NZ function but still it doesnt work...

Please do help.....

Thanks

 

[boblarson]

You have:

1. (Nz(Nz([Order Details].Quantity,0)+

two NZ's where there should be one

2. The NZ should be at the point where you are assigining the Alias:

Nz([Order Details].Quantity,0) AS [Order Details_Quantity], Nz([Order Details Petshop].Quantity,0) AS [Order Details Petshop_Quantity]

 

[confuse]

You have:

1. (Nz(Nz([Order Details].Quantity,0)+

two NZ's where there should be one

2. The NZ should be at the point where you are assigining the Alias:

Nz([Order Details].Quantity,0) AS [Order Details_Quantity], Nz([Order Details Petshop].Quantity,0) AS [Order Details Petshop_Quantity]

But still it doesnt work..

Please see attached for sample..Thanks