Microsoft KB Archive/208368

= ACC2000: Parsing Titles Beginning with "A," "An," or "The" =

Article ID: 208368

Article Last Modified on 7/15/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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.  Start Microsoft Access and create a new blank database. 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. 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

  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  On the File menu, click Close and Return to Microsoft Access. Create a new query based on the Books table.</li> On the Query menu, click Update Query.</li> 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.</li> 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.</li></ol>

<div class="references_section">