MSAccess97: SELECT in ORDER BY?

[stlaural]

I don't think this is optimal anyway, as it runs the same query two times. Why don't you just order by columns number, this way :

SELECT a.MyID
,(SELECT MAX(b.MyDate) FROM MyTable2 b WHERE b.MyID=a.MyID) AS MyDateField 
FROM MyTable1 f 
ORDER BY 2 DESC, 1 DESC