View Single Post
  #2  
Old May 14th, 2010, 03:05 PM posted to microsoft.public.access.forms
Boggle
external usenet poster
 
Posts: 10
Default Order By/Filter clause corrupted with aliases

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?

"Boggle" wrote:

I have a datasheet form (actually it's a subform, but this happens if you
look at the subform on its own). It has a SQL query for a record source
which joins three tables, and uses aliases (AS) for those tables. The
displayed field from two of the tables has the same name in each. (The SQL
is generated from table definitions and it has to handle the possibility that
the same table will be referenced in two different joins (example
employee-location, employee-manager-location), so aliasing is not
optional.) All works fine.

Then you use the built-in sorting feature to sort one of the fields (one
which shares a name with another joined table) ... still fine. Go into
design view and look at Order By, and it looks like:
[Lookup_control].[field]
That's OK.

Then hit save, and Access takes it upon itself to rewrite the clause into
something it can't recognise:
Lookup_control.[alias.field]

Doh. Presumably it thinks the alias is necessary because of the shared
name, though of course it isn't, because we have the control name. And why
are there two separate pieces of code writing that clause?

You can carry on, and switch between form view and design view no problem.
But when you close and reopen the form, you get:
Invalid bracketing of name 'Lookup_control.[alias.field]'
and the form will not open. (As a subform, it comes up blank.)

Filtering triggers the same problem.

Clearly this is a bug, but I don't know how to report it. I could read the
Order By and Filter clauses on load and try and patch them up but that seems
a bit horrible. Any ideas?