title: "Invalid use of Null" in Access Query
body: <p>I have two near-identical databases (the second was a copy of the first, and then modified with different company/product information). There is an aging report that never worked right, that I had to fix. I have it working on one database (A), but the second one (B) kicks up the "Invalid" error.</p>
  
  <p>The report query is generated from another query, which basically grabs the order information and customer information. From there, the query in question breaks up the orders into chunks depending on how long ago they were shipped, and returns the balance. The error message pops up with the following code segment:</p>
  
  <pre><code>Round(Sum(IIf((Date()-[ShipDate] Is Null),[LineTotal]+(CLng([LineTotal]*[SalesTaxRate]*100)/100)+[FreightCharge]-nz([Total Payments]),0)),2) AS Current_Unshipped
  </code></pre>
  
  <p>If the Is Null is changed to anything else, the query executes normally. If I try and introduce any "if the variable is null" code, it throws up the error. I tried the following, but to no avail.</p>
  
  <pre><code>Round(Sum(IIf((nz[ShipDate]),[LineTotal]+(CLng([LineTotal]*[SalesTaxRate]*100)/100)+[FreightCharge]-nz([Total Payments]),0)),2) AS Current_Unshipped
  </code></pre>
  
  <p>I have another column that throws back "NULL" if the shipdate is null, which suprisingly works. The first code segment works flawlessly in the other database, so I'm confused. I need to be able to generate a column for the unshipped orders, hence the missing [ShipDate]. I'm at a loss as to why the code does not work.</p>
  
  <p>The whole </p>
  
  <pre><code>(CLng([LineTotal]*[SalesTaxRate]*100)/100)+[FreightCharge]-nz([Total Payments]),0))
  </code></pre>
  
  <p>segment of the code is how the balance is generated, and it works. It's also implemented in about a million places in the database- The designer thought to auto-generate the total balance due every time the query is ran, instead of storing it somewhere.</p>
  
  <p>The entire query code is posted below as well. If anyone can point me in the right direction, it'd be of great help. </p>
  
  <pre><code>SELECT DISTINCTROW 
      Round(Sum(IIf((Date()-[ShipDate]&lt;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])&lt;61 And (Date()-[ShipDate]&gt;30),[LineTotal]+(CLng([LineTotal]*[SalesTaxRate]*100)/100)+[FreightCharge]-nz([Total Payments]),0)),2) AS [31-60 Days], 
      Round(Sum(IIf((Date()-[ShipDate])&lt;91 And (Date()-[ShipDate]&gt;60),[LineTotal]+(CLng([LineTotal]*[SalesTaxRate]*100)/100)+[FreightCharge]-nz([Total Payments]),0)),2) AS [61-90 Days],
      Round(Sum(IIf((Date()-[ShipDate])&gt;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))&gt;0 Or (Round(Sum(nz([LineTotal])+(CLng(nz([LineTotal])*nz([SalesTaxRate])*100)/100)+nz([FreightCharge])-nz([Total Payments])),2))&lt;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))&gt;0 Or (Round(Sum(nz([LineTotal])+(CLng(nz([LineTotal])*nz([SalesTaxRate])*100)/100)+nz([FreightCharge])-nz([Total Payments])),2))&lt;0) AND (([Receivables Aging Report Query].ShipDate) Is Null))
  ORDER BY 
      [Receivables Aging Report Query].OrderID;
  </code></pre>