title: Unexplained 'Invalid Operation' error in Access query with SQL backend
body: <p>I am trying to migrate the entire backend of an Access application onto SQL Server. The first part of my project involves moving all of the tables whilst making minimum changes after the migration (no SQL Views, Pass-through queries etc. yet).</p>
  
  <p>I have two queries in particular that I am using here:</p>
  
  <p><strong>ProductionSystemUnAllocatedPurchases</strong> - Which executes and returns a resultset successfully.</p>
  
  <p><a href="https://i.stack.imgur.com/EoGVJ.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/EoGVJ.png" alt="ProductionSystemUnAllocatedPurchases_Screenshot"></a> This is the full formula (sorry its extremely complex) for QtyAvailableOnPurchase:</p>
  
  <p><strong>QtyAvailableOnPurchase:</strong> I believe this field could be the problem here?</p>
  
  <pre><code>IIf((IIf([Outstanding Qty]&gt;([P-ORDER-T with Qty Balance]![QTY]-[SumOfQty]), 
  ([P-ORDER-T with Qty Balance]![QTY]-[SumOfQty]),[Outstanding Qty]))&gt;0, 
  (IIf([Outstanding Qty]&gt;([P-ORDER-T with Qty Balance]![QTY]-[SumOfQty]),([P- 
  ORDER-T with Qty Balance]![QTY]-[SumOfQty]),[Outstanding Qty])),0)
  </code></pre>
  
  <p><strong>ProductionSystemUnAllocatedPurchasesTotal</strong> - Which gives an 'Invalid Operation' error message</p>
  
  <p><a href="https://i.stack.imgur.com/1ws7A.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/1ws7A.png" alt="ProductionSystemUnAllocatedPurchasesTotal_Screenshot"></a></p>
  
  <p>Now the strange thing for me is that the first query works perfectly fine, but the second which uses the first as a source table, gives me this error message when executing. This query works perfectly fine with an access backend, but fails with SQL Server tables.
  Any Ideas?</p>