Access Nz() function doesn't work in query

Question

I have lots of orders in tblOrders and a few deliveries in tblDeliveries.

SELECT tblOrders.SkuBestelId, Sum(tblDeliveries.delivered) AS TotalDelivered
FROM tblOrders
INNER JOIN tblDeliveries ON tblOrders.SkuBestelId = tblDeliveries.SkuBestelId
GROUP BY tblOrders .SkuBestelId;

Of course, this gives me lots of "TotalDelivered" fields with NULL values. Since Access will return a NULL value if I use those in any kind of sum, I need to get a zero. I thought to create a field like this:

GrandTotal: Nz([TotalDelivered], 0)

but it didn't work: improper syntax error.

I also tried putting the Sum() function directly in the Nz() function, but that didn't do it, either.

Answer 1

Firstly, if you were to use the query as specified above, this would not return null values, as you are using an INNER JOIN, unless the actual delivered field contains null values.

So, lets say you meant to use LEFT JOIN you could try

SELECT tblOrders.SkuBestelId, Sum(NZ([delivered],0)) AS TotalDelivered
FROM tblOrders LEFT JOIN 
tblDeliveries ON tblOrders.SkuBestelId = tblDeliveries.SkuBestelId
GROUP BY tblOrders.SkuBestelId;