Output format of Nz() function (1 Viewer)

Darrell

Registered User.
Local time
Today, 11:35
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.
 

Darrell

Registered User.
Local time
Today, 11:35
Joined
Feb 1, 2001
Messages
299
Hi Adam, yeah I finally thought about this properly last night and problem solved.

Thanks to jal also for suggestion of specific conversion function.
 

Users who are viewing this thread

Top Bottom