The simplest solution is to:
1. Backup the BE
2. Update all the null values to 0
3. Change the table to make the numeric field required.
4. You need to decide if you want to force a value by leaving the default null or add 0 as the default. Not all numbers should automatically default to 0.
If you don't make the field required and specify 0 as the default, you MUST always handle nulls in all queries that do calculations or concatenations with these fields.
Things like OrderQty might make more sense to default to 1 since why place an order for a zero quantity. The price being 0 is different since there are reasons for not charging for an item.
In the meantime, see if this helps.
SELECT [Order Details].BatchNo, Orders.OrderID, [Order Details].OrderDetailID, Orders.CustomerName, Orders.OrderNo, Orders.NCONo, [Order Details].ProductNo, ([Order Details].OrderQty, Sum(Nz([OrderQty],1) * Nz([Price],0)) AS OrderTotal, [Acknwoledge order].intcoterms, Orders.[Exchange rate2], Nz([intcoterms],0) * Nz([exchange rate2], 1) AS Expr1
FROM (Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order Details].OrderID) INNER JOIN [Acknwoledge order] ON Orders.NCONo = [Acknwoledge order].[NCO No]
GROUP BY [Order Details].BatchNo, Orders.OrderID, [Order Details].OrderDetailID, Orders.CustomerName, Orders.OrderNo, Orders.NCONo, [Order Details].ProductNo, [Order Details].OrderQty, [Acknwoledge order].intcoterms, Orders.[Exchange rate2];
I had [Exchange Rate2] also default to 1 so there would be no conversion. Do what makes sense.
Also note that both OrderQty and [Exchange rate2] are in the group by.
And finally, the Group By makes no sense. It seems to include the PK of the OrderDetails table so no aggregation will ever take place.
