 | Familiar Sight | | Join Date: Mar 2008 Location: Southern California
Posts: 221
| |
here's my sql code: - 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
-
FROM ActivityLog
-
WHERE (((Month([DateSignedIN]))=[Forms]![frmDRInput]![Month]) AND ((Year([DateSignedIN]))=[Forms]![frmDRInput]![Year]))
-
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???
|
