Thanks! I tried over and over with different forms. Until I stopped using
the alias, it would not work. Now I understand. Thanks again, this should
help with other potential issues.

Dave


"onedaywhen" wrote:


dave k wrote:
The thing I can't do is replace the GROUP BY with fchar. I must
use GROUP BY MID(F1,1,1). Is this because fchar is not defined yet
when the
SQL statement is performed?

According to the ANSI specs what *should* happen is that when a column
alias is used in the SELECT clause, that alias is the *only* way of
referring to the column/expression (e.g. in the ORDER BY and GROUP BY
clauses) because the underlying tables should not be subsequently used
i.e. the newly-build cursor should be used instead.

Jet patently does not follow the spec because the opposite is true i.e.
you cannot use the alias, you have to use the original column name (or
full expression) and this usage should be illegal. [I'm not sure how
Jet works under the covers but perhaps it cannot optimize the
expression so it *must* go back to the original tables?]

So you *should* be able to use the alias but with Jet SQL you can't.
FWIW SQL Server is similarly non-compliant [and is similarly file-based
under the covers].

Jamie.

--