Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Create a Custom Sort Order

ACC2000: How to Create a Custom Sort Order

View products that this article applies to.

This article was previously published under Q304564
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies to a Microsoft Access database (.mdb) and to a Microsoft Access project (.adp).

↑ Back to the top

Summary

This article shows you how to create a custom sort order by using the Switch function.

Sometimes, you may want Microsoft Access to sort based on a custom sort order (that is, not ascending or descending, but a different, user-specified sort order).

For example, the Northwind sample database contains an employees table in which an employee may have one of four possible titles. Access sorts these titles alphabetically as:
  • Inside Sales Coordinator
  • Sales Manager
  • Sales Representative
  • Vice President, Sales
However, you may want to sort these titles with a custom sort order, such as:
  • Vice President, Sales
  • Sales Manager
  • Inside Sales Coordinator
  • Sales Representative

↑ Back to the top

More information

To create a custom sort order on the Employees table in the Northwind Sample Database, follow these steps:
  1. Start Microsoft Access and open the Northwind.mdb sample database.
  2. On the View menu, point to Database Objects, and then click Queries.
  3. Click New.
  4. Click Design View, and then click OK.
  5. In the Show Table dialog box, click the Employees table, and then click Close.
  6. Add the following fields to the query grid by either double-clicking the field names, or by dragging them into the columns of the query grid: 
       Query: qryCustomSort
   ---------------------------------------------------------
   Type:  Select Query

   Field: EmployeeID
   Table: Employees

   Field: LastName
   Table: Employees

   Field: FirstName
   Table: Employees

   Field: Title
   Table: Employees

   NOTE: In the following example, an underscore (_) is used as a
   line-continuation character. Remove the underscore from the end 
   of the line when re-creating the example.

   Field: Customsort:Switch([title]="Vice President,Sales","a", _
          [title]="Sales Manager","b", _
          [title]="Inside Sales Coordinator","c", _
          [title]="Sales Representative","d") 
   Table: Employees
   Sort:  Ascending
   Show:  Unchecked
  7. Save the query as qryCustomSort. Run the query.
Note that the employees are now sorted as follows:
  • Vice President, Sales
  • Sales Manager
  • Inside Sales Coordinator
  • Sales Representative

↑ Back to the top

References

For more information about the Switch function, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type switch function in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

↑ Back to the top

Keywords: KB304564, kbhowto

↑ Back to the top

Article Info
Article ID : 304564
Revision : 2
Created on : 6/25/2004
Published on : 6/25/2004
Exists online : False
Views : 244