Re: Modified Access query does not respect ORDER!

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On Jun 18, 5:55 pm, "Rick Brandt" <rickbran...@xxxxxxxxxxx> wrote:
zofficede...@xxxxxxxxxxx wrote:
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 data*** view of the query.
If you use your query in a situation where it is not being opened as a
data*** (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 data*** view
when the query is "make-table." If then reverted to SELECT, the bug
can be recreated.

Even if this malady exists only for data*** 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.
.

Quantcast