ACC2000: Parsing Titles Beginning with "A," "An," or "The" (208368)


The information in this article applies to:

  • Microsoft Access 2000
This article was previously published under Q208368
Moderate: Requires basic macro, coding, and interoperability skills.

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

SUMMARY

Titles often begin with the articles "A," "An," and "The." When you sort such title fields, you may not get the intended result of having the titles sorted in alphabetical order by their most meaningful words. For example, movie titles, such as "The Bronx," appear under "T" rather than under "B." To sort titles by a word other than "A," "An," or "The," use the function provided in this article in an update query to modify the data in a table by either removing the article completely or moving it to the end of the title.

MORE INFORMATION

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.
  1. Start Microsoft Access and create a new blank database.
  2. Create a new table with only one field as follows:

    Field Name: Title
    Data Type: Text
    Field Size: 50

    Save the table as Books. When prompted to define a primary key, click No.
  3. Open the Books table in Datasheet view, and then type the following titles:

    A Bridge Too Far
    All In The Family
    An Anthropology of Prose
    And We Were Young
    The Old Man and The Sea
    Then There Were None

  4. Create a new module, and then type the following code in the module window:
    Function ParseArticle(strOldTitle As String, Optional varKeepArticle _
   As Variant) As String
' strOldTitle is the field or value you want to parse.
' varKeepArticle is an optional variant value that, when left blank,
' will completely remove the article (for example, "The Beatles" becomes
' "Beatles.")

On Error GoTo Err_Result
Dim intLength As Integer, strArticle As String

If IsMissing(varKeepArticle) Then
   varKeepArticle = False
End If

intLength = Len(strOldTitle)
strArticle = ""

' Check Value for preceding article (a, an, or the).
If Left(strOldTitle, 2) = "a " Then
   strArticle = ", " & Left(strOldTitle, 1)
   strOldTitle = Right(strOldTitle, intLength - 2)
ElseIf Left(strOldTitle, 3) = "an " Then
   strArticle = ", " & Left(strOldTitle, 2)
   strOldTitle = Right(strOldTitle, intLength - 3)
ElseIf Left(strOldTitle, 4) = "the " Then
   strArticle = ", " & Left(strOldTitle, 3)
   strOldTitle = Right(strOldTitle, intLength - 4)
End If

' If varKeepArticle is TRUE, then add the article string to the end.
If varKeepArticle Then
 ParseArticle = strOldTitle & strArticle
Else
 ParseArticle = strOldTitle
End If

Exit Function

Err_Result:
  ParseArticle = "#Error"
End Function
  5. On the File menu, click Close and Return to Microsoft Access.
  6. Create a new query based on the Books table.
  7. On the Query menu, click Update Query.
  8. Add the following information to the first column in the query grid:

    Field: Title
    Table: Books
    Update To: ParseArticle([Title],1)

    When you use "1" as a variable in the Update To field, this makes the function place the articles at the end of the titles, after a comma. If you omit the "1," the articles are deleted.
  9. Run the query. Note that when you view the Books table, the articles are at the end of the titles, following a comma. The titles that had no leading articles remain unchanged.

REFERENCES

For additional information about sorting lists with leading articles, click the article number below to view the article in the Microsoft Knowledge Base:

210556 ACC2000: How to Sort Titles Without Including the Leading Articles (The, A, or An)


For additional information about manipulating text strings, click the article number below to view the article in the Microsoft Knowledge Base:

209045 ACC2000: Sample Expressions to Extract Portion of Text String

Modification Type:MinorLast Reviewed:7/15/2004
Keywords:kbhowto kbinfo KB208368
©2004 Microsoft Corporation. All rights reserved.