Modified Access query does not respect ORDER!

[zofficedepot]

In Access 2003 (please try others too):
Create Table1 with alpha fields fname, lname (no primary key)
Enter 2 records: Clueless Gates and Smart Anybody
Create a query in SQL or design:

SELECT Table1.fname, Table1.lname
FROM Table1
ORDER BY Table1.fname;

CLOSE AND SAVE as Query1, and THEN run it. You get Gates, then Anybody
(because it's sorting by first names Clueless and Smart).
With Gates highlighted, click the A-to-Z sort button. Now Anybody is
atop.
Close the query. Answer Yes to the save changes question.

Now comes the queerness: subsequent invocations are sorted by lname.
But the SQL says it uses fname. So does the QBE.

WTF??

 

[pietlinden]

In Access 2003 (please try others too):
Create Table1 with alpha fields fname, lname (no primary key)
Enter 2 records: Clueless Gates and Smart Anybody
Create a query in SQL or design:

SELECT Table1.fname, Table1.lname
FROM Table1
ORDER BY Table1.fname;

CLOSE AND SAVE as Query1, and THEN run it. You get Gates, then Anybody
(because it's sorting by first names Clueless and Smart).
With Gates highlighted, click the A-to-Z sort button. Now Anybody is
atop.
Close the query. Answer Yes to the save changes question.

Now comes the queerness: subsequent invocations are sorted by lname.
But the SQL says it uses fname. So does the QBE.

WTF??

sounds like Access is saving and applying a filter to the original
query, not modifying the ORDER BY clause.

 

[zofficedepot]

In Access 2003 (please try others too):

sounds like Access is saving and applying a filter to the original
query, not modifying the ORDER BY clause.

Well what can I or anyone do? There isn't even a "(filtered)"
indication on the status bar or window caption bar.

This is truly serious. Even a Microsoft developer or designer should
comprehend that. Software that provides the exact opposite result of
what you request? That's serious. (Unless there is no remaining
serious enterprise that takes Access seriously.)

 

[Rick Brandt]

In Access 2003 (please try others too):
Create Table1 with alpha fields fname, lname (no primary key)
Enter 2 records: Clueless Gates and Smart Anybody
Create a query in SQL or design:

SELECT Table1.fname, Table1.lname
FROM Table1
ORDER BY Table1.fname;

CLOSE AND SAVE as Query1, and THEN run it. You get Gates, then Anybody
(because it's sorting by first names Clueless and Smart).
With Gates highlighted, click the A-to-Z sort button. Now Anybody is
atop.
Close the query. Answer Yes to the save changes question.

Now comes the queerness: subsequent invocations are sorted by lname.
But the SQL says it uses fname. So does the QBE.

WTF??

I believe the filter only applies to the actual datasheet view of the query.
If you use your query in a situation where it is not being opened as a
datasheet (for a form or report) I don't think that filter will apply.

 

[zofficedepot]

I believe the filter only applies to the actual datasheet view of the query.
If you use your query in a situation where it is not being opened as a
datasheet (for a form or report) I don't think that filter will apply.

Creating a report with the wizard uses the "invisible" order by lname.
While the Sorting and Grouping dialog is empty, the Report Properties
shows lname. Clearing the report order (in report properties) causes
it to use fname.

Creating a report in design view, upon specifying Query1, it fills in
Order By with Query1.lname. As before, clearing that causes it to use
fname.

Interestingly, changing this to a "make-table" (SELECT INTO) and
running it creates a table in the desired (i.e. specified) order
(fname). Oddly, the A-Z and Z-A icons are disabled in datasheet view
when the query is "make-table." If then reverted to SELECT, the bug
can be recreated.

Even if this malady exists only for datasheet view of SELECT queries
this is technologically unacceptable and Woody's Watch would make high
theater of it. I hope that someone channeled into the process gives
this due priority. For crying out loud, light up the sort icon, or
something.

 

[Rick Brandt]

Creating a report with the wizard uses the "invisible" order by lname.
While the Sorting and Grouping dialog is empty, the Report Properties
shows lname. Clearing the report order (in report properties) causes
it to use fname.

Creating a report in design view, upon specifying Query1, it fills in
Order By with Query1.lname. As before, clearing that causes it to use
fname.

Interestingly, changing this to a "make-table" (SELECT INTO) and
running it creates a table in the desired (i.e. specified) order
(fname). Oddly, the A-Z and Z-A icons are disabled in datasheet view
when the query is "make-table." If then reverted to SELECT, the bug
can be recreated.

Even if this malady exists only for datasheet view of SELECT queries
this is technologically unacceptable and Woody's Watch would make high
theater of it. I hope that someone channeled into the process gives
this due priority. For crying out loud, light up the sort icon, or
something.

While I agree with your point this behavior has been there since at least
Access 97 and few people have ever noticed it much less had problems caused
by it. The priority MS would give to changing it (if any at all) would be
very low I'm sure.