Cannot sort on calculated field in Query or Report

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;
 
C

Chris O'C via AccessMonster.com

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


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;
 
S

saraqpost

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





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;

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
 
Top