Order By/Filter clause corrupted with aliases
Have you tried to do the SQL in a query rather than in the form?
Build a little, test a little.
It gets worse. I removed the aliases just to see what happened. I am still
referencing two fields with the same name in different tables. The Order By
clause is still fine until you save:
and when you save it goes to:
where table1 is always the first joined table with that field name. That
that still works if you sort in the column relating to the first joined table
(so apparently it isn't a syntax problem any more). But if you sort in the
column from the second joined table, then the control doesn't refer to
table1, and you get the invalid bracket error. (Though clearly it isn't
the brackets which are the problem.)
By the way my controls are combo boxes so you can drop down and see a list
of all valid entries in the column. But I can't see what that would have to
do with it.
It all worked fine in Access 2003.
By the way this is Access 2007.
I just wrote a simple cleanup routine (within any pair of square brackets,
split by periods, and replace the contents with the last token), but the
syntax check occurs before Open and Load. Which means there is no workaround
- if you do the edit at any other time then you have to save the form ... and
Access kindly screws the clause up again as it saves.
I'm surprised this hasn't come up before - surely there are other people out
there who use datasheets, and not completely trivial joins? With users who
like to sort and filter?