Cannot sort on calculated field in Query or Report

  • Thread starter Thread starter saraqpost
  • Start date Start date
S

saraqpost

Hi -

I have a field, calculated in a query, and I want to write a report
to show "Sell Thru Pct", Descending.

Query runs fine, UNTIL I put the sort in -then I get "Expression is
typed incorrectly or too complex....Simplify....."

I CAN run the query, with a Sort Desc on another calculated field:
EI.

Same problem in the report - I can sort Desc on EI, and CANNOT sort on
the Percent field.

Any ideas? All the posts I found seem to indicate that I should be
able to sort on a field that is calcuated in the query (it didn't work
when I tried to sort on an expression to calculate the percent -
though I may have done that incorrectly)

Query code below, with my heartfelt appreciation!
sara

SELECT [qrySalesInRange-TotByLine].StockNum, [T: Stock Tracking Cost
Info].StockName, [qrySalesInRange-TotByLine].StNum, [qrySalesInRange-
TotByLine].SumOfSales AS Sales, [qrySalesInRange-
TotByLine].SumOfReturns AS Returns, [qrySalesInRange-
TotByLine].SumOfNetSales AS NetSales, [qrySalesInRange-
TotByLine].SumOfSalesUnits AS Units, [qrySalesInRange-
TotByLine].SumOfReturnUnits AS RetUnits, [qrySalesInRange-
TotByLine].SumOfNetUnits AS NetUnits,
qryTotalPurchByLineSeasonYear.SumOfUnitsPurchased AS PurchUnits,
qryTotalPurchByLineSeasonYear.Season,
qryTotalPurchByLineSeasonYear.SalesYear, [SumOfUnitsPurchased]-
[SumOfNetUnits] AS EI, [SumOfNetUnits]/[SumOfUnitsPurchased] AS
PctSellThru
FROM (qryTotalPurchByLineSeasonYear RIGHT JOIN [qrySalesInRange-
TotByLine] ON qryTotalPurchByLineSeasonYear.StockNum =
[qrySalesInRange-TotByLine].StockNum) LEFT JOIN [T: Stock Tracking
Cost Info] ON qryTotalPurchByLineSeasonYear.StockNum = [T: Stock
Tracking Cost Info].StockNum
GROUP BY [qrySalesInRange-TotByLine].StockNum, [T: Stock Tracking Cost
Info].StockName, [qrySalesInRange-TotByLine].StNum, [qrySalesInRange-
TotByLine].SumOfSales, [qrySalesInRange-TotByLine].SumOfReturns,
[qrySalesInRange-TotByLine].SumOfNetSales, [qrySalesInRange-
TotByLine].SumOfSalesUnits, [qrySalesInRange-
TotByLine].SumOfReturnUnits, [qrySalesInRange-
TotByLine].SumOfNetUnits,
qryTotalPurchByLineSeasonYear.SumOfUnitsPurchased,
qryTotalPurchByLineSeasonYear.Season,
qryTotalPurchByLineSeasonYear.SalesYear
HAVING ((([qrySalesInRange-TotByLine].StNum)=99) AND
((qryTotalPurchByLineSeasonYear.Season)=[Forms]!
[frmSamplesPurchaseReports]![cboSeason]) AND
((qryTotalPurchByLineSeasonYear.SalesYear)=[Forms]!
[frmSamplesPurchaseReports]![cboSalesYear]))
ORDER BY [SumOfNetUnits]/[SumOfUnitsPurchased] DESC;
 
When an expression is too complex to sort on, use an ordinal column.  Since
your expression is the 14th column, try this sort:

ORDER BY 14 DESC;

Chris
Microsoft MVP





[email protected] said:
Hi -
Click to expand...
I have a field, calculated  in a query, and I want to write a report
to show "Sell Thru Pct", Descending.
Click to expand...
Query runs fine, UNTIL I put the sort in -then I get "Expression is
typed incorrectly or too complex....Simplify....."
Click to expand...
I CAN run the query, with a Sort Desc on another calculated field:
EI.
Click to expand...
Same problem in the report - I can sort Desc on EI, and CANNOT sort on
the Percent field.
Click to expand...
Any ideas?  All the posts I found seem to indicate that I should be
able to sort on a field that is calcuated in the query (it didn't work
when I tried to sort on an expression to calculate the percent -
though I may have done that incorrectly)
Click to expand...
Query code below, with my heartfelt appreciation!
sara
Click to expand...
SELECT [qrySalesInRange-TotByLine].StockNum, [T: Stock Tracking Cost
Info].StockName, [qrySalesInRange-TotByLine].StNum, [qrySalesInRange-
TotByLine].SumOfSales AS Sales, [qrySalesInRange-
TotByLine].SumOfReturns AS Returns, [qrySalesInRange-
TotByLine].SumOfNetSales AS NetSales, [qrySalesInRange-
TotByLine].SumOfSalesUnits AS Units, [qrySalesInRange-
TotByLine].SumOfReturnUnits AS RetUnits, [qrySalesInRange-
TotByLine].SumOfNetUnits AS NetUnits,
qryTotalPurchByLineSeasonYear.SumOfUnitsPurchased AS PurchUnits,
qryTotalPurchByLineSeasonYear.Season,
qryTotalPurchByLineSeasonYear.SalesYear, [SumOfUnitsPurchased]-
[SumOfNetUnits] AS EI, [SumOfNetUnits]/[SumOfUnitsPurchased] AS
PctSellThru
FROM (qryTotalPurchByLineSeasonYear RIGHT JOIN [qrySalesInRange-
TotByLine] ON qryTotalPurchByLineSeasonYear.StockNum =
[qrySalesInRange-TotByLine].StockNum) LEFT JOIN [T: Stock Tracking
Cost Info] ON qryTotalPurchByLineSeasonYear.StockNum = [T: Stock
Tracking Cost Info].StockNum
GROUP BY [qrySalesInRange-TotByLine].StockNum, [T: Stock Tracking Cost
Info].StockName, [qrySalesInRange-TotByLine].StNum, [qrySalesInRange-
TotByLine].SumOfSales, [qrySalesInRange-TotByLine].SumOfReturns,
[qrySalesInRange-TotByLine].SumOfNetSales, [qrySalesInRange-
TotByLine].SumOfSalesUnits, [qrySalesInRange-
TotByLine].SumOfReturnUnits, [qrySalesInRange-
TotByLine].SumOfNetUnits,
qryTotalPurchByLineSeasonYear.SumOfUnitsPurchased,
qryTotalPurchByLineSeasonYear.Season,
qryTotalPurchByLineSeasonYear.SalesYear
HAVING ((([qrySalesInRange-TotByLine].StNum)=99) AND
((qryTotalPurchByLineSeasonYear.Season)=[Forms]!
[frmSamplesPurchaseReports]![cboSeason]) AND
((qryTotalPurchByLineSeasonYear.SalesYear)=[Forms]!
[frmSamplesPurchaseReports]![cboSalesYear]))
ORDER BY [SumOfNetUnits]/[SumOfUnitsPurchased] DESC;
Click to expand...
Click to expand...

This still produced the same error.
Why is this expression too complex?
Is there any way to sort "ordinally" in the report?
If all this is "no", do I have to output to a temp table and then sort
on the field in the report (This works - I tried it)
OR do some calculations in a prior query, then do the pct calc in a
final query? (This still does not work - I tried it)

From what I've read, there is no way to sort just in the report on the
calculated field - I have to calculate it in a query, right?

I got it to work with the Temp Table routine (make table query), but I
don't really understand WHY, or if this is my ONLY option/soluiton.

Thanks very much, Chris.

Sara
 
Back
Top