vba 访问 Nz() 函数在查询中不起作用
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/2071566/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access Nz() function doesn't work in query
提问by skerit
I have lots of orders in tblOrders and a few deliveries in tblDeliveries.
我在 tblOrders 中有很多订单，在 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:
当然，这给了我很多带有 NULL 值的“TotalDelivered”字段。因为如果我在任何类型的总和中使用这些值，Access 将返回一个 NULL 值，所以我需要得到一个零。我想创建一个这样的字段：
GrandTotal: Nz([TotalDelivered], 0)
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.
我还尝试将 Sum() 函数直接放在 Nz() 函数中，但也没有这样做。
回答by Adriaan Stander
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.
首先，如果您要使用上面指定的查询，则不会返回空值，因为您使用的是INNER JOIN，除非实际交付的字段包含空值。
So, lets say you meant to use LEFT JOINyou could try
因此，假设您打算使用LEFT JOIN，您可以尝试
SELECT tblOrders.SkuBestelId, Sum(NZ([delivered],0)) AS TotalDelivered
FROM tblOrders LEFT JOIN
tblDeliveries ON tblOrders.SkuBestelId = tblDeliveries.SkuBestelId
GROUP BY tblOrders.SkuBestelId;
