Re: Sorting Query Results - Advice Please

Tech-Archive recommends: Fix windows errors by optimizing your registry


You can enter an order by in the grid directly.

If you enter it into the grid you would need to enter a calculated field

Field: NameOrder: [FirstName] is not null
Show: Unchecked
Sort: Ascending

No criteria.

If you enter a query in SQL view, you can USUALLY select VIEW: Design from the menu and see how you would have to do it using the design view. I said usually because there are some queries that cannot be displayed or constructed using the design view.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Dermot wrote:
Hi John
Thanks for the reply and explanation.

I have resolved the problem using the information you provided but would like to ask you for further advice to clarify my understanding.

Quote:
ORDER BY [FirstName] Is Not Null, [LastName], [FirstName]

Can the ORDER BY Clause only be entered directly into the SQL Satement.
I.E View | SQL View and manually enter the details to the clause.

Someone said to me that it can be entered directly from the grid...would that be via the criteria field?




"John Spencer" wrote:

I understood your request slightly different than Fred.

If you want all records sorted by last name (but in two groups)
Last Names with First Names
followed by
Last Names with NO first Name

Insert field in the query in the first position and set it as follows
SortWithFirst: [First Name] is Not Null
Sort By: Ascending

In the ORDER BY clause of the SQL you would have

ORDER BY [FirstName] Is Not Null, [LastName], [FirstName]

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================


fredg wrote:
On Fri, 5 Dec 2008 14:23:06 -0800, Dermot wrote:

I have created a table with Firstname and Lastname fields.
Then I created a query to sort alphabetically by Lastname and then Firstname order.

Some entries do not have a first name and these appear at the top of query result.

Question
How can I create a condition to place the "Null" Firstname results to the bottom of the returned list, rather than the top?
Add another column.
SortFirstName:IIf(IsNull([FirstName]),"zzzzzz",[FirstName])

Sort on LastName and this column instead of FirstName.


.

Quantcast