访问查询中的"Invalid use of Null"

Question

我有两个几乎相同的数据库（第二个是第一个数据库的副本，然后用不同的公司/产品信息修改）。有一份老化的报告从来都不起作用，我不得不修复它。我让它在一个数据库（A）上工作，但第二个数据库（B）会出现"无效"错误。

报告查询是由另一个查询生成的，该查询基本上获取订单信息和客户信息。从那里开始，有问题的查询根据订单的发货时间将订单分解为块，并返回余额。错误消息弹出，代码段如下：

Round(Sum(IIf((Date()-[ShipDate] Is Null),[LineTotal]+(CLng([LineTotal]*[SalesTaxRate]*100)/100)+[FreightCharge]-nz([Total Payments]),0)),2) AS Current_Unshipped

如果将Is Null更改为其他值，则查询将正常执行。如果我尝试引入任何"如果变量为null"的代码，就会抛出错误。我尝试了以下方法，但没有成功。

Round(Sum(IIf((nz[ShipDate]),[LineTotal]+(CLng([LineTotal]*[SalesTaxRate]*100)/100)+[FreightCharge]-nz([Total Payments]),0)),2) AS Current_Unshipped

我有另一个列，如果发货日期为NULL，它会返回"NULL"，这令人惊讶地有效。第一个代码段在另一个数据库中完美地工作，所以我很困惑。我需要能够为未发货的订单生成一列，因此缺少[ShipDate]。我不知道为什么代码不起作用。

整个

(CLng([LineTotal]*[SalesTaxRate]*100)/100)+[FreightCharge]-nz([Total Payments]),0))

代码的一部分是如何生成余额的，它是如何工作的。它还在数据库中的大约一百万个地方实现——设计者认为每次运行查询时都会自动生成到期的总余额，而不是将其存储在某个地方。

整个查询代码也发布在下面。如果有人能为我指明正确的方向，那将是非常有帮助的。

SELECT DISTINCTROW 
    Round(Sum(IIf((Date()-[ShipDate]<31),[LineTotal]+(CLng([LineTotal]*[SalesTaxRate]*100)/100)+[FreightCharge]-nz([Total Payments]),0)),2) AS [Current],
    Round(Sum(IIf((Date()-[ShipDate] Is Null),[LineTotal]+(CLng([LineTotal]*[SalesTaxRate]*100)/100)+[FreightCharge]-nz([Total Payments]),0)),2) AS Current_Unshipped,
    Round(Sum(IIf((Date()-[ShipDate])<61 And (Date()-[ShipDate]>30),[LineTotal]+(CLng([LineTotal]*[SalesTaxRate]*100)/100)+[FreightCharge]-nz([Total Payments]),0)),2) AS [31-60 Days], 
    Round(Sum(IIf((Date()-[ShipDate])<91 And (Date()-[ShipDate]>60),[LineTotal]+(CLng([LineTotal]*[SalesTaxRate]*100)/100)+[FreightCharge]-nz([Total Payments]),0)),2) AS [61-90 Days],
    Round(Sum(IIf((Date()-[ShipDate])>90,[LineTotal]+(CLng([LineTotal]*[SalesTaxRate]*100)/100)+[FreightCharge]-nz([Total Payments]),0)),2) AS [91+ Days],
    Round(Sum(nz([LineTotal])+(CLng(nz([LineTotal])*nz([SalesTaxRate])*100)/100)+nz([FreightCharge])-nz([Total Payments])),2) AS Balance,
    [Receivables Aging Report Query].CompanyName, 
    [Receivables Aging Report Query].OrderDate, 
    [Receivables Aging Report Query].ShipDate, 
    [Receivables Aging Report Query].OrderID, 
    nz((Round(Date()-[ShipDate])),"NULL") AS Span, 
FROM [Receivables Aging Report Query]
GROUP BY 
    [Receivables Aging Report Query].CompanyName, 
    [Receivables Aging Report Query].OrderDate, 
    [Receivables Aging Report Query].ShipDate, 
    [Receivables Aging Report Query].OrderID
HAVING(((Round(Sum(nz([LineTotal])+(CLng(nz([LineTotal])*nz([SalesTaxRate])*100)/100)+nz([FreightCharge])-nz([Total Payments])),2))>0 Or (Round(Sum(nz([LineTotal])+(CLng(nz([LineTotal])*nz([SalesTaxRate])*100)/100)+nz([FreightCharge])-nz([Total Payments])),2))<0) AND (([Receivables Aging Report Query].ShipDate) Is Not Null)) OR (((Round(Sum(nz([LineTotal])+(CLng(nz([LineTotal])*nz([SalesTaxRate])*100)/100)+nz([FreightCharge])-nz([Total Payments])),2))>0 Or (Round(Sum(nz([LineTotal])+(CLng(nz([LineTotal])*nz([SalesTaxRate])*100)/100)+nz([FreightCharge])-nz([Total Payments])),2))<0) AND (([Receivables Aging Report Query].ShipDate) Is Null))
ORDER BY 
    [Receivables Aging Report Query].OrderID;

Answer 1

使用此：

Round(Sum(IIf(DateDiff("d",Date(),[ShipDate])=0,[LineTotal]+CLng([LineTotal]*[SalesTaxRate]*100)/100+[FreightCharge]-Nz([Total Payments],0),0)),2) AS Current_Unshipped