SQL IIF Isnull 和更新集 = 0

Question

我的参考是 如何将所有记录的空白（null）值替换为0？

目标：我有空白查询输出。图像0 我希望打印出 0 代替。

问题：当我尝试IIF Isnull时，我收到一个提示消息框，要求输入我不想看到的参数值。 图片1

PARAMETERS [BeginDate] DateTime, [EndDate] DateTime;
SELECT Sum(dbo_SO_SalesHistory.DollarsSold) AS SumOfDollarsSold, IIF(ISNULL([SumOfDollarsSold]), 0, [SumOfDollarsSold])
FROM dbo_SO_SalesHistory
WHERE (((dbo_SO_SalesHistory.InvoiceDate) Between [BeginDate] And [EndDate]) AND ((dbo_SO_SalesHistory.CustomerNo)="M"));

这是我在运行 SQL 后的查询：图像 2

然后我尝试了UPDATE

PARAMETERS [BeginDate] DateTime, [EndDate] DateTime;
SELECT Sum(dbo_SO_SalesHistory.DollarsSold) AS SumOfDollarsSold
UPDATE [dbo_SO_SalesHistory.DollarsSold] SET [SumOfDollarsSold] = 0
FROM dbo_SO_SalesHistory
WHERE (((dbo_SO_SalesHistory.InvoiceDate) Between [BeginDate] And [EndDate]) AND ((dbo_SO_SalesHistory.CustomerNo)="M")) AND [SumOfDollarsSold] IS NULL;

它给了我The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.错误消息：图像3

关于我使用的任何方法的任何建议将不胜感激。谢谢！

Answer 1

PARAMETERS [BeginDate] DateTime, [EndDate] DateTime;
SELECT IIF(ISNULL(Sum(dbo_SO_SalesHistory.DollarsSold)),0,Sum(dbo_SO_SalesHistory.DollarsSold)) AS SumOfDollarsSold
FROM dbo_SO_SalesHistory
WHERE (((dbo_SO_SalesHistory.InvoiceDate) Between [BeginDate] And [EndDate]) AND ((dbo_SO_SalesHistory.CustomerNo)="M"))

执行时的查询不知道列SumOfDollarsSold是什么。 因此，您要么必须将其作为subquery并测试值，要么可以在IIf语句中使用实际的聚合函数。

或者，出于显示目的，也许更好的事情是FORMAT()函数，但请注意，该数字实际上是一个字符串。 我不太记得访问的实现，但它可能是这样的：

PARAMETERS [BeginDate] DateTime, [EndDate] DateTime;
SELECT FORMAT(Sum(dbo_SO_SalesHistory.DollarsSold)),0) AS SumOfDollarsSold
FROM dbo_SO_SalesHistory
WHERE (((dbo_SO_SalesHistory.InvoiceDate) Between [BeginDate] And [EndDate]) AND ((dbo_SO_SalesHistory.CustomerNo)="M"))

https://support.office.com/en-us/article/Format-Property-Number-and-Currency-Data-Types-ca77795e-b40d-4abb-b42f-daa0bb709620?ui=en-US&rs=en-US&ad=US