Formatted fields in ORDER BY using DISTINCT

[toon10]

Hi

I have the following SQL which I run from a VB front end onto an Access database and it gives an error saying "ORDER BY clause conflicts with DISTINCT"

SELECT DISTINCT
`Part No`, `Selling Unit`, `Customer No`, Description, `Prod GP`,
Format(`Cost Price`, 'Currency') AS `Cost Price`,
Format(`Latest Price`, 'Currency') AS `Latest Price`,
`Prices Type`, Format(`Latest Price` / 100 * '5', 'Currency')
AS `Uplift To Add`, Format(`Uplift To Add` + `Latest Price`,
'Currency') AS Uplift, Format((Uplift - `Cost Price`), 'Currency')
AS Margin, `Product Manager`, `Effective From Date`,
`Creation Date`, `Condition Type`
FROM tblPrice
WHERE 1 = 1 AND
`Effective From Date` <= # 25 / 11 / 2005 # AND
`Product Manager` = 'Kevin Harris' AND `Bus Unit` = 'BA' AND
`Prices Type` = 'International' AND (`Part No` LIKE '%K')
ORDER BY `Latest Price` DESC

If I remove the DISTINCT I get a few duplicate records which I want rid of. If I remove the ORDER BY clause the DISTINCT works but I lose my sort order.

My query will let me use DISTINCT and ORDER BY on any of the normal fields like `Part No` and `Selling Unit` but it doesn't like to ORDER BY on the calculated fields or the fields that have Format conditions on.

Is there a way around this?

Thanks
Andrew

 

[lupins46]

Have you tried:
Order By Format(`Latest Price`, 'Currency')

 

[toon10]

Ah, that's it.

Thanks for the help, much appreciated.