ACC: Parsing Titles Beginning with "A," "An," or "The" (95/97)Last reviewed: October 3, 1997Article ID: Q154600 |
The information in this article applies to:
SUMMARYModerate: 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 INFORMATIONThe 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 FunctionThis function will check for article words at the beginning of a title and will make the desired changes to the title.
REFERENCESFor 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
© 1998 Microsoft Corporation. All rights reserved. Terms of Use. |