I have hit the Access 97 problem of ORDER BY not in target list with a
simple, one-table query. The problem does not seem to have anything to do
with joins.
I have logged the SQL sent to the back end with the CommLog option in the
238 driver (very useful, thanks Byron). When using ORDER BY, Access 97
first sends a query to retrieve just the key(s) and then sends another to
get the required data. Hence, for the first query unless the fields used
in ORDER BY are key fields they are not in the target list. Nice one MS!
Nothing like making your software do the obvious thing, is there?
There is a work around. First create your query the usual way (point and
click if you like). Next, display the SQL. Finally, convert it into a
PASSTHROUGH query, so that the backend receives your SQL as written. This
worked for my very simple test, but I have not checked it out for more
complex queries.
BTW, Access 2.0 does not seem to have this problem, so why this strange
behaviour has been introduced into 97 I cannot imagine.
Tony Cowderoy
