ACC: Sorting Titles Without Leading Articles (The, An, or A)
ID: Q98665
|
The information in this article applies to:
-
Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97
SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.
This article shows you how you can sort titles in Text fields without
including the leading articles "the," "an," or "a."
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
"Building Applications with Microsoft Access" 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 "Introduction to Programming" manual in Microsoft
Access version 1.x or the "Building Applications" 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 "An", "A", "The" ' 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.
REFERENCES
For more information about parsing strings, please see the following
article in the Microsoft Knowledge Base:
Q115915
Sample Expressions to Extract Portion of Text String
Additional query words:
titles sort strip left
Keywords : kbprg ExrStrg
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbinfo