Microsoft KB Archive/110391

= ACC: Sample Function to Capitalize Words and Phrases =

Article ID: 110391

Article Last Modified on 5/6/2003

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition

-



This article was previously published under Q110391





SUMMARY
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article describes a sample user-defined function called Proper that you can use to capitalize the first character of a word or set of words.

This article contains two versions of the Proper function:


 * One version is used in the AfterUpdate property of a control on a form, primarily for data entry.
 * The other version is used in calculated query fields, calculated controls on forms and reports, macro SetValue expressions, and Access Basic expressions.

NOTE: Microsoft Access version 2.0 includes a sample Proper function in the sample database SOLUTION.MDB. The Proper function is located in the Proper module. The NWIND.MDB also includes this sample function in the Utility Functions module.

NOTE: Microsoft Access for Windows 95 has a new function, StrConv, that provides this functionality. For more information about the StrConv function, search for "StrConv function" using the Microsoft Access for Windows 95 Help Index.

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 on Access Basic, please refer to the "Introduction to Programming" manual.



MORE INFORMATION
To create the Proper function, enter the following code in a module:

  '********************************************************** ' Declarations section of the module '**********************************************************     Option Explicit '==========================================================  ' The following function is designed for use in the AfterUpdate ' property of form controls. ' Features: '   - Leading spaces do not affect the function's performance. '   - "O'Brian" and "Wilson-Smythe" will be properly capitalized. ' Limitations: '   - It will change "MacDonald" to "Macdonald." '   - It will change "van Buren" to "Van Buren." '   - It will change "John Jones III" to "John Jones Iii." '==========================================================  Function Proper (AnyValue As Variant) As Variant Dim ptr As Integer Dim TheString As String Dim currChar As String, prevChar As String

If IsNull(AnyValue) Then Exit Function End If

TheString = CStr(AnyValue) For ptr = 1 To Len(TheString)        ' Go through each char. in                                           ' string. currChar = Mid$(TheString, ptr, 1)   ' Get the current character.

Select Case prevChar              ' If previous char. is a                                           ' letter,'this char. should be                                           ' lowercase. Case "A" To "Z", "a" To "z" Mid(TheString, ptr, 1) = LCase(currChar)

Case Else Mid(TheString, ptr, 1) = UCase(currChar)

End Select prevChar = currChar Next ptr AnyValue = theString End Function

How to Use This Function in the AfterUpdate Property of a Control

 * 1) Create a form based on a table or query.
 * 2) Type the following line in the AfterUpdate property of a text box control:

=Proper

For example, if the ControlName is [Full Name], type the following:

=Proper([Full Name])

How to Modify This Function for Expressions and Calculated Controls
 Enter the code shown above to create the Proper function.  Modify the following section of code

        AnyValue= theString End Function

To read as follows:

        Proper=CVar(theString) End Function



Examples
The examples below all require the second version of the Proper function.

Using Proper in a Query:

  Field: Full Name:Proper([Last Name] & " " & [First Name])

This will concatenate the first and last names and capitalize the first letter of each.

Using Proper in a Calculated Control on a Form or Report:

  TextBox -  ControlName: AddressP ControlSource: =Proper([Address])

NOTE: In calculated fields, the ControlName must be unique.

Using Proper in a Macro:

  Action: SetValue Item: Screen.ActiveControl

Expression: Proper(Screen.ActiveControl)

NOTE: You can call this macro from the AfterUpdate property of a control on a form. This has the same affect as the first version of the Proper function.

<div class="references_section">