Microsoft KB Archive/98665

= ACC: Sorting Titles Without Leading Articles (The, An, or A) =

Article ID: 98665

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q98665



Moderate: Requires basic macro, coding, and interoperability skills.



SUMMARY
This article shows you how you can sort titles in Text fields without including the leading articles &quot;the,&quot; &quot;an,&quot; or &quot;a.&quot;

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the &quot;Building Applications with Microsoft Access&quot; manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the &quot;Introduction to Programming&quot; manual in Microsoft Access version 1.x or the &quot;Building Applications&quot; manual in Microsoft Access version 2.0



MORE INFORMATION
The following is a list of book titles:

Aztec Indians

The Ant People

Beast Man

The Baseball Club

An Attempt At Fun

Apple Valley

If you sort the above list using a custom function in a query, you receive the following new list:

The Ant People

Apple Valley

An Attempt At Fun

Aztec Indians

The Baseball Club

Beast Man

To have book titles returned without the leading article, create the following function: Option Explicit

Function Gettitle (Titles) As String sp = InStr(1, Titles, Chr$(32)) If sp > 0 Then Select Case Left(Titles, sp - 1) Case &quot;An&quot;, &quot;A&quot;, &quot;The&quot;   ' add any other articles here! Gettitle = Mid(Titles, sp + 1) Case Else Gettitle = Titles End Select Else Gettitle = Titles End If  End Function To sort a table on the Titles field, create a query with the following two columns in the query grid:     Query -  Field: Sorted List: Gettitle([Titles]) Sort: Ascending Show: Yes Field: [Titles] Show: No The first column is a calculated field that extracts the text of the Titles field, but omits the leading article. The results of this calculation are sorted in ascending order. The second Show check box is cleared so that the query result is not displayed.

