Sort query a by a calculated control

[gg80]

Using aA2007. I have an append query that I would like to sort by the expression:

Corr_Final: IIf([handicapSystem]=2,[Corr_TonT],[Corr_TonD])

Corr_TonT and Cor_TonD are calculated values. When I try to sort query using Corr_Final, I get a message about parameter values for the two options.. I found one website that claimed that I should be able to sort, but it didn't work.



Is it possible to sort or do I have to create another query just to be able to sort?

Any help much appreciated.

[June7]

Post the full query SQL statement.

If this query is an aggregate type, then won't work. Would have to repeat the calcs for Corr_TonT and Corr_TonD in the Corr_Final expression.

Do you have link to that site?

[gg80]

Thanks for quick reply. It isn't an aggregate query (As I understand it, aggregate means queries that include groupby, avg etc). The site I was looking at and couldn't understand is: http://msgroups.net/microsoft.public...ed-field/95440

The sql:
SELECT RaceResultsRawData.id, RaceResultsRawData.SponsorYC, RaceResultsRawData.Year, RaceResultsRawData.Series, RaceResultsRawData.RaceNo, RaceResultsRawData.Fleet, RaceResultsRawData.handicapSystem, IIf([handicapSystem]=2,[Corr_TonT],[Corr_TonD]) AS Corr_Final, [elapsed]-[PHRF]*[distance]/86400 AS Corr_TonD, [elapsed]*[A]/([B]+[PHRF]) AS corr_TonT, ([findate1]-[startdate1]+[finishtime]-[startTime]) AS Elapsed, RaceResultsRawData.phrf, 3600/([elapsed]/[distance]) AS speed, RaceResultsRawData.status, RaceResultsRawData.[Boat Class]
FROM RaceResultsRawData
ORDER BY RaceResultsRawData.SponsorYC, RaceResultsRawData.Year, RaceResultsRawData.Series, RaceResultsRawData.RaceNo, RaceResultsRawData.Fleet, IIf([handicapSystem]=2,[Corr_TonT],[Corr_TonD]);

[June7]

Okay, guess I've never had to do filter/sort like this. It is possible to refer to the calculated fields in other calculations to construct another field, but apparently not in the WHERE or ORDER BY clauses.

Yes, can apply filter/sort on calculated field (I have done that) but if that calc includes calculated fields, have to repeat the calcs (which I just confirmed in a test).

[gg80]

Thanks so much for quick and detailed follow up. -Not what I hoped to hear, but I can quit looking and get back to doing it.