Darrell
Registered User.
- Local time
- Today, 01:52
- Joined
- Feb 1, 2001
- Messages
- 299
I have a query which has three fields using the Nz() function from three different tables. In the tables these fields are stored as numbers. In the query however, the value doesn't seem to be output as a number as: 1) I cant change the format to standard and 2) if I add two of these fields together it appends rather than adds. ie, 1000 + 1000 = 10001000
Here is the query in its entirity for reference
SELECT [qry Latest Stocktake].[Last Stocktake Date], [qry Latest Stocktake].[Stock Location], Products.InventoryCode, Products.SAPCode, Nz([Quantity],0) AS [Stocktake Qty], Nz([Qty Received],0) AS [Receival Qty], Nz([Qty Dispatched],0) AS [Dispatched Qty]
FROM ([qry SOH - Incomings] RIGHT JOIN ([qry Latest Stocktake] RIGHT JOIN Products ON [qry Latest Stocktake].InventoryCode = Products.InventoryCode) ON [qry SOH - Incomings].[Inventory Code] = Products.InventoryCode) LEFT JOIN [qry SOH - Outgoings] ON Products.InventoryCode = [qry SOH - Outgoings].InventoryCode
GROUP BY [qry Latest Stocktake].[Last Stocktake Date], [qry Latest Stocktake].[Stock Location], Products.InventoryCode, Products.SAPCode, Nz([Quantity],0), Nz([Qty Received],0), Nz([Qty Dispatched],0)
ORDER BY Products.InventoryCode;
Any help to get this output in numeric form would be greatly appreciated.
Here is the query in its entirity for reference
SELECT [qry Latest Stocktake].[Last Stocktake Date], [qry Latest Stocktake].[Stock Location], Products.InventoryCode, Products.SAPCode, Nz([Quantity],0) AS [Stocktake Qty], Nz([Qty Received],0) AS [Receival Qty], Nz([Qty Dispatched],0) AS [Dispatched Qty]
FROM ([qry SOH - Incomings] RIGHT JOIN ([qry Latest Stocktake] RIGHT JOIN Products ON [qry Latest Stocktake].InventoryCode = Products.InventoryCode) ON [qry SOH - Incomings].[Inventory Code] = Products.InventoryCode) LEFT JOIN [qry SOH - Outgoings] ON Products.InventoryCode = [qry SOH - Outgoings].InventoryCode
GROUP BY [qry Latest Stocktake].[Last Stocktake Date], [qry Latest Stocktake].[Stock Location], Products.InventoryCode, Products.SAPCode, Nz([Quantity],0), Nz([Qty Received],0), Nz([Qty Dispatched],0)
ORDER BY Products.InventoryCode;
Any help to get this output in numeric form would be greatly appreciated.