Help and Support

ACC: Parsing Titles Beginning with "A," "An," or "The" (95/97)

View products that this article applies to.
Article ID:154600
Last Review:January 19, 2007
Revision:4.1
This article was previously published under Q154600
Moderate: Requires basic macro, coding, and interoperability skills.

SUMMARY

Titles often begin with the article "A," "An," or "The." When you sort such title fields, you may not get the desired result of having the titles sorted in alphabetical order by their most meaningful words. For example, movie titles, such as "The Bronx," appear in the t's rather than the b's. To sort titles by a word other than "A," "An," or "The," use the function provided in this article in a query to build a field that will either remove the article completely or move it to the end.

Back to the top

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. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

The function's syntax is as follows: 
   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

This function will check for article words at the beginning of a title and will make the desired changes to the title.

Back to the top

REFERENCES

For more information about parsing strings, please see the following article in the Microsoft Knowledge Base:

115915 (http://support.microsoft.com/kb/115915/EN-US/) ACC: Sample Expressions to Extract Portion of Text String

Back to the top

APPLIES TO
•Microsoft Access 95 Standard Edition
•Microsoft Access 97 Standard Edition

Back to the top

Keywords: 
kbhowto KB154600

Back to the top

Article Translations

 

Related Support Centers

Other Support Options

  • Contact Microsoft
    Phone Numbers, Support Options and Pricing, Online Help, and more.
  • Customer Service
    For non-technical assistance with product purchases, subscriptions, online services, events, training courses, corporate sales, piracy issues, and more.
  • Newsgroups
    Pose a question to other users. Discussion groups and Forums about specific Microsoft products, technologies, and services.