If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Order By/Filter clause corrupted with aliases
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? |
Thread Tools | |
Display Modes | |
|
|