By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,779 Members | 1,291 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,779 IT Pros & Developers. It's quick & easy.

strange sql error msg

n8kindt
100+
P: 221
here's my sql code:

Expand|Select|Wrap|Line Numbers
  1. SELECT ActivityLog.DateSignedIN, Sum(ActivityLog.ProductPurchased) AS SumOfProductPurchased, Sum(ActivityLog.ProductAdj) AS SumOfProductAdj, Sum(ActivityLog.TaxPercent) AS SumOfTaxPercent, Sum(ActivityLog.Subtotal) AS SumOfSubtotal, Sum(ActivityLog.GrandTotal) AS SumOfGrandTotal, Sum(ActivityLog.TransactionType) AS SumOfTransactionType, Sum(Round((((([ProductPurchased] + Nz([ProductAdj],0))*([TaxPercent]*2)) + (([Subtotal]-([ProductPurchased] + Nz([ProductAdj],0)))*[TaxPercent]) + [Subtotal])+Nz([Shipping],0))+0.000001,2)) AS ExpectedGrandTotal, Sum(Round(Nz([GrandTotal],[ExpectedGrandTotal]),2)) AS FinalGT, Sum(ActivityLog.Shipping) AS SumOfShipping, Sum(Round((([TaxPercent]*([Subtotal] - ([ProductPurchased] + Nz([ProductAdj],0)))) + (2*([ProductPurchased] + Nz([ProductAdj],0))*[TaxPercent]) + 0.00000001),2)) AS ExpectedTax, Sum(ActivityLog.ActualTax) AS SumOfActualTax, Sum(Round(Nz([ActualTax],[ExpectedTax]),2)) AS FinalTax, Sum([subtotal]-[productpurchased]) AS Supplies
  2. FROM ActivityLog
  3. WHERE (((Month([DateSignedIN]))=[Forms]![frmDRInput]![Month]) AND ((Year([DateSignedIN]))=[Forms]![frmDRInput]![Year]))
  4. GROUP BY ActivityLog.DateSignedIN;
Here's the error msg:
Subqueries cannot be used in the expression (Round(Nz([GrandTotal],[ExpectedGrandTotal]),2)).

if i hide the 'FinalGT' and the 'ExpectedTax' columns (FinalGT is the one with the troublesome code--i have to hide ExpectedTax, too b/c it returns a similar error msg), it runs fine.... which is wierd b/c 'ExpectedGT' and 'FinalGT' use the same format but they run fine... uhhhh why am i getting this error msg???
May 9 '08 #1
Share this Question
Share on Google+
2 Replies


nico5038
Expert 2.5K+
P: 3,072
I'm afraid you can't use the "as" fields in the same query. You would have to replace them by the calculation used to create them.
An alternative is to save the query without this field and use the saved query in a new query where you place the calculation.

Nic;o)
May 9 '08 #2

n8kindt
100+
P: 221
I'm afraid you can't use the "as" fields in the same query. You would have to replace them by the calculation used to create them.
An alternative is to save the query without this field and use the saved query in a new query where you place the calculation.

Nic;o)
that makes sense. thanks nico!
May 9 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.