Re: Modified Access query does not respect ORDER!

Tech-Archive recommends: Repair Windows Errors & Optimize Windows 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 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.
.

Relevant Pages

  • Re: Form wont remember last value
    ... "Ken Snell (MVP)" wrote: ... "I am using the LName and FName parameters as the Control Source for ... Report SQL: ...
    (microsoft.public.access.formscoding)
  • Re: Form wont remember last value
    ... textboxes in both the report and subreport. ... named LName and FName, and I can see no typos in the query with either the ... form name or control names. ...
    (microsoft.public.access.formscoding)
  • Re: I need help w an Update Query
    ... FName fields to the grid. ... Join the two tables on EMPLID. ... From the menu, select query and Update ... > I have two Tables, both tables have the same 3 Fields EmplID, LName, ...
    (microsoft.public.access.queries)
  • Re: Query in AS 400
    ... I am trying to accomplish something in a query on the AS400. ... is LNAME is SMITH and FNAME is BOB ... I would like to create it as SMITH, BOB ...
    (comp.sys.ibm.as400.misc)
  • Re: Query in AS 400
    ... I am trying to accomplish something in a query on the AS400. ... is LNAME is SMITH and FNAME is BOB ... I would like to create it as SMITH, BOB ...
    (comp.sys.ibm.as400.misc)