Sort Column Versus 'Order By' Query Property
Sometimes your queries appear sorted in the wrong order in datasheet view in
MS Access. You check the Sort column(s) and the order they appear in and
everything looks fine. You
switch to SQL view and examine ORDER BY clause in
the SQL statement and it’s perfect. What’s going on?
Queries in MS Access actually have one more place to set sort order for
viewing in datasheet view. You probably never look there but you may have
inadvertently set it while you were in datasheet view. When you view a table
or query in datasheet view you can set a ‘temporary’ sort order by clicking
on a column and then on the Sort Ascending or Sort Descending button.
Usually this sort order is temporary, but when you close the view you may
get a message box asking you if you’d like to save the changes to the design
of your query. If you say yes, the sort order is saved; not in SQL but as a
query property. Not only is it saved, but it overrides whatever sort
column(s) you’ve chosen for your query (the ORDER BY clause in SQL) in
datasheet view. If you pass the results to a report, export the results
directly to Excel, Word, ADO or ADO.NET, you won’t have any problem.
To view the Order By query property, open your query in design view and
then right-click anywhere in the grey area above the query columns.
You should see the Query Properties box below. Typically, the Order
By property is blank. If your queries aren’t sorting properly, make sure
it’s blank!
Are You Ready For Office 365?
Office 365 is
- Email and calendaring powered by Microsoft Exchange
- Office Web Apps - the office productivity apps you already know, tailored for the
web
- Websites powered by Microsoft SharePoint both for internal-facing intranet and customer-facing
website
- Instant Messaging and Online Meetings powered by Microsoft Lync
- All with a financially-backed 99.9% uptime guarantee
Sign up for a free trial:
Your information will not be shared with any other party. You must provide
a valid email in order to recieve your login information and starting information
kit.
Watch
this video to see how Office 365 can help your business.
An even more annoying issue manifests if you happen to have sorted on a
column that is a filter criteria in a parameter query: the parameter box
pops up twice! Notice below that the IPAddress column has a parameter to request
the IP Address to filter but the column does not have anything in the sort
field. The column Time is being used as a
sort column.
However, in the query properties window just above query columns you can see the Order
By property has been set to sort by IPAddress!
When you run your parameter query, a message box pops up and (in this
case) asks you to ‘Enter IP Address’ to filter on.
The query is run once using your input IP Address. But what happens when
the query results are displayed in datasheet view? Design view runs the
query again and asks for the parameter again! If you run parameter queries
and get asked for the parameter twice, be sure to check the Order By query
property in your query and clear it.
|
|