Result table sorted w/right click on column header causes sort to.

[NoMagic@Certegy]

Problem existed in 97 and unfortunately 2000 as well.

Once a query's results are shown, right clicking a column header and sorting
causes the sort criteria to be remembered though the underlying query hasn't
changed at all. From that point forward Access INSISTS the the output be
sorted in the way it was sorted when the results table was sorted. If I had
wanted the sort as a permanent part of the query, I would have put it in the
orginal SQL.

This is INCREDIBLY annoying.

Anybody know how to squash this 'feature'?

 

[Van T. Dinh]

Go to the DesignView of the Query. Open the Properties window of the Query
and delete the text in the Order By row in the Properties window.

 

[NoMagic@Certegy]

I don't use the QBE interface, instead write the SQL directly. Are you
saying I have to go into the formatted panel and keep removing all the items
Access adds 'for' me? This is very debilitating. Each time I change a field
in a query, ex: sum(cpu) gets changed to sum(cpu) as [Net CPU], Access
INSISTS that I tell it what the value for EXPR001 is, which was the system
assigned name for the results of the original SUM function when it didn't
have the "AS" clause, it REMEMBERS it for some insane reason, and forces me
to provide it...of course it is meaningless so nothing could be typed in that
will make the query work. Just closing Access and restarting will not fix
it, it still asks for the value, so the machine has to be re-booted to get it
to 'forget' to ask for the value for EXPR001. '97 occassionaly did this, but
this version is nuts about it.

Any help anybody can provide would be greatly appreciated.

 

[Van T. Dinh]

Whether you use SQL or the grid through the interface, it is still a Query.
The "manual sort" you did will be saved with the Query.

I don't have problems with Access renaming the Alias I used but I think
there is a scenario that can cause what you saw:

Let say you create a Query (SQL or grid) and save it with the Defsault Alias
"Expr0001". You then opens it and does a "manual sort" on "Expr0001" then
close it. At this stage, Access will remember/save the sort with the Field
name "Expr0001" in the "Order By" Property.

If you then opens the Design / SQL View of the Query and rename the Alias,
Access will accepts the new Alias but if will NOT change the Field name /
Alias used in the "Order By" Property. When you run the Query, it will
think that "Expr0001" is a Parameter (since it is no longer in your
selection list) and Access will ask for its value.

See my previous post on how to remove the "Order By" entry.