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

提示:将鼠标放在中文语句上可以显示对应的英文。显示中英文
时间:2020-09-11 11:13:00  来源:igfitidea点击:

Access Nz() function doesn't work in query

sqlms-accessvbafunctionnull

提问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;