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.
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.
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.
Back to the top
