Access Tip of the Day
Alphabetize a recordset by either of two fields (Access 97/2000/2002)
There may be times when you'd like to alphabetize data based on an alternate field if the one you primarily sort by is blank. For example, you might want to sort a query by either company name or a contact's last name. Unlike a traditional sort, you don't want to just group all of the records that lack company names at the beginning of the datasheet. Instead, if the CompanyName field is blank, you want Access to use the contact's last name in its place. In other words, you want to treat a person's last name as if it were a company name when the company is missing, to create a datasheet that looks like:
CompanyName FirstName LastName
--------------------- --------- --------
All-Weather Wear Inc. Claire Zemeckis
Larry Anderson
Beckett Motor Company Brian Brault
To accomplish such a sort, create an extra query field specifically to provide the custom sort, then use the NZ() function to replace the contents of one field for Null values in another. For instance, let's say you're working with a query using the previously described fields. Open the query in Design view and remove any previously existing sort specifications. Then, enter the following in a blank Field text box:
SortValue: NZ([CompanyName],[LastName])
Finally, select Ascending from the new column's Sort dropdown list. When you run the query, the NZ() function returns the contents in LastName instead if CompanyName is Null.
(Content provided by Element K Journals)
(Note: Your browser is set to refuse cookies. As a result, you may frequently see previously-viewed tips)
Print
CloseCopyright © 2001 - 2025 MJ Technologies, LLC. All rights reserved.