Microsoft KB Archive/175390: Difference between revisions
(importing KB archive) |
m (Text replacement - "&" to "&") |
||
(One intermediate revision by the same user not shown) | |||
Line 12: | Line 12: | ||
<div id="TitleRow"> | <div id="TitleRow"> | ||
= <span id="KB175390"></span>ACC2: How to Parse Titles Beginning with | = <span id="KB175390"></span>ACC2: How to Parse Titles Beginning with "A," "An," or "The" = | ||
Line 51: | Line 51: | ||
== SUMMARY == | == SUMMARY == | ||
Titles often begin with the article | 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.<br /> | ||
<br /> | <br /> | ||
This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Access Basic, please refer to the | This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Access Basic, please refer to the "Building Applications" manual. | ||
</div> | </div> | ||
Line 67: | Line 67: | ||
' strOldTitle is the field or value you want to parse. | ' strOldTitle is the field or value you want to parse. | ||
' varKeepArticle is a variant value that, when False, completely | ' varKeepArticle is a variant value that, when False, completely | ||
' removes the article (for example, | ' removes the article (for example, "The Beatles" becomes "Beatles"). | ||
' If varKeepArticle is True, the function places the article at the | ' If varKeepArticle is True, the function places the article at the | ||
' end of the string returned by the function (for example, | ' end of the string returned by the function (for example, | ||
' | ' "The Beatles" becomes "Beatles, The"). | ||
Line 76: | Line 76: | ||
Dim intLength As Integer, strArticle As String | Dim intLength As Integer, strArticle As String | ||
intLength = Len(strOldTitle) | intLength = Len(strOldTitle) | ||
strArticle = | strArticle = "" | ||
' Check Value for preceding article (a, an, or the). | ' Check Value for preceding article (a, an, or the). | ||
If Left(strOldTitle, 2) = | If Left(strOldTitle, 2) = "a " Then | ||
strArticle = | strArticle = ", " & Left(strOldTitle, 1) | ||
strOldTitle = Right(strOldTitle, intLength - 2) | strOldTitle = Right(strOldTitle, intLength - 2) | ||
ElseIf Left(strOldTitle, 3) = | ElseIf Left(strOldTitle, 3) = "an " Then | ||
strArticle = | strArticle = ", " & Left(strOldTitle, 2) | ||
strOldTitle = Right(strOldTitle, intLength - 3) | strOldTitle = Right(strOldTitle, intLength - 3) | ||
ElseIf Left(strOldTitle, 4) = | ElseIf Left(strOldTitle, 4) = "the " Then | ||
strArticle = | strArticle = ", " & Left(strOldTitle, 3) | ||
strOldTitle = Right(strOldTitle, intLength - 4) | strOldTitle = Right(strOldTitle, intLength - 4) | ||
Line 94: | Line 94: | ||
If varKeepArticle Then | If varKeepArticle Then | ||
ParseArticle = strOldTitle & | ParseArticle = strOldTitle & strArticle | ||
Else | Else | ||
ParseArticle = strOldTitle | ParseArticle = strOldTitle | ||
Line 102: | Line 102: | ||
Err_Result: | Err_Result: | ||
ParseArticle = | ParseArticle = "#Error" | ||
End Function | End Function | ||
</pre> | </pre> | ||
Line 115: | Line 115: | ||
[[../115915|115915]] ACC: Sample Expressions to Extract Portion of Text String<br /> | [[../115915|115915]] ACC: Sample Expressions to Extract Portion of Text String<br /> | ||
<br /> | <br /> | ||
For information on how to parse titles beginning with | For information on how to parse titles beginning with "A," "An," or "The" in Microsoft Access versions 7.0 and 97, please see the following article in the Microsoft Knowledge Base:<br /> | ||
<br /> | <br /> | ||
[[../154600|154600]] ACC: Parsing Titles Beginning with | [[../154600|154600]] ACC: Parsing Titles Beginning with "A," "An," or "The" (95/97) | ||
</div> | </div> |
Latest revision as of 12:30, 21 July 2020
Article ID: 175390
Article Last Modified on 11/6/2000
APPLIES TO
- Microsoft Access 2.0 Standard Edition
This article was previously published under Q175390
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.
This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Access Basic, please refer to the "Building Applications" manual.
MORE INFORMATION
NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.
Function ParseArticle(strOldTitle As String, varKeepArticle _ As Variant) As String ' strOldTitle is the field or value you want to parse. ' varKeepArticle is a variant value that, when False, completely ' removes the article (for example, "The Beatles" becomes "Beatles"). ' If varKeepArticle is True, the function places the article at the ' end of the string returned by the function (for example, ' "The Beatles" becomes "Beatles, The"). On Error GoTo Err_Result Dim intLength As Integer, strArticle As String 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 Then ParseArticle = strOldTitle & strArticle Else ParseArticle = strOldTitle End If Exit Function Err_Result: ParseArticle = "#Error" End Function
REFERENCES
For more information about parsing strings, please see the following article in the Microsoft Knowledge Base:
115915 ACC: Sample Expressions to Extract Portion of Text String
For information on how to parse titles beginning with "A," "An," or "The" in Microsoft Access versions 7.0 and 97, please see the following article in the Microsoft Knowledge Base:
154600 ACC: Parsing Titles Beginning with "A," "An," or "The" (95/97)
Additional query words: remove ignore parse
Keywords: kbhowto kbusage KB175390