ACC: Sample Function to Capitalize Words and Phrases

• Microsoft Access versions 1.0, 1.1, 2.0

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 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(<control name>)
   

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

         =Proper([Full Name])
   

How to Modify This Function for Expressions and Calculated Controls

1. Enter the code shown above to create the Proper() function.

2. 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.

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

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

   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.

REFERENCES

For more information about methods of calling a function, please see the following articles in the Microsoft Knowledge Base:

   ARTICLE-ID: Q97514
   TITLE     : ACC: Writing Functions Called from Events or Expressions

   ARTICLE-ID: Q115915
   TITLE     : ACC: Sample Expressions to Extract Portion of Text String