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

Last reviewed: October 3, 1997
Article ID: Q154600
The information in this article applies to:
  • Microsoft Access versions 7.0, 97

SUMMARY

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

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.

MORE INFORMATION

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.

REFERENCES

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

   ARTICLE-ID: Q115915
   TITLE     : ACC: Sample Expressions to Extract Portion of Text String


Additional query words: remove ignore parse
Keywords : GnlFrmt kbusage
Version : 2.0 7.0 97
Platform : WINDOWS
Hardware : x86
Issue type : kbhowto


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: October 3, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.