Displaying a null value as 0

[jordanturner]

Hi all,

I am very nearly finished my first stock control database but I still have a couple of problems. I have a report that runs a calculation for stock quantity on hand. The calculation looks through the transactions table and sums all received - all dispatched +returns. The report works fine apart from if an item has not had a transaction, then it does not show on the report. I would like the report to show the item as 0 rather then not appear at all.


The SQL for the report is:


SELECT DISTINCTROW tblSupplier.SupplierID, tblSupplier.CompanyName, Sum(nz([QuantityReceived])-nz([QuantityDispatched]-nz(QuantityReturned))) AS [Units in Stock], tblStock.ItemName, tblStock.StockID
FROM tblSupplier INNER JOIN (tblStock INNER JOIN tblInventoryTransactions ON tblStock.StockID = tblInventoryTransactions.StockID) ON tblSupplier.SupplierID = tblStock.SupplierID
GROUP BY tblSupplier.SupplierID, tblSupplier.CompanyName, tblStock.ItemName, tblStock.StockID;

Any help would be greatly appreciated.

Thanks

Jordan T

[Cullen1109]

SELECT qryStockDate2.StockID, qryStockDate2.ItemName, IIf(IsNull([qryStockDate].[LastStock]),0,[qryStockDate].[LastStock]) AS LastStock, qryStockDate2.NewStock, qryStockDate2.SupplierID
FROM qryStockDate RIGHT JOIN qryStockDate2 ON qryStockDate.StockID=qryStockDate2.StockID;

Using that sort of idea help after our little chat?
Solved!

[jordanturner]

Thanks, you are a genius!!!! You should be an admin