DetailPage-MSS-KB

Microsoft small business knowledge base

Article ID: 302499 - Last Review: June 19, 2014 - Revision: 4.0

 
Moderate: Requires basic macro, coding, and interoperability skills.

For a Microsoft Access 2000 version of this article, see 253911  (http://support.microsoft.com/kb/253911/EN-US/ ) .

For a Microsoft Access 2002 version of this article, see 298607  (http://support.microsoft.com/kb/298607/EN-US/ ) .

On This Page

Summary

This article provides examples for using a built-in function called StrConv that you can use to capitalize the first character of a word or a set of words. This function is not affected by leading spaces, but it does have the following limitations for some names:
  • Changes "MacDonald" to "Macdonald."
  • Changes "van Buren" to "Van Buren."
  • Changes "James Pratt VI" to "James Pratt Vi."

More information

The StrConv function can be used to change the case of a string to uppercase, to lowercase, or so that the first letter is uppercase. The syntax is StrConv(string, conversion), where "string" is the text string and "conversion" is 1, 2, or 3. For "conversion", uppercase is 1, lowercase is 2, and 3 makes the first letter of each word uppercase.

When writing the StrConv function in Visual Basic for Applications (VBA), instead of the integers 1, 2, or 3, you can also use one of the following constants:
vbUpperCase Converts the entire string to uppercase.
vbLowerCase Converts the entire string to lowercase.
vbProperCase Converts the first letter of each word to uppercase and the remaining letters to lowercase.
NOTE: The StrConv function has more constants than just the three that are mentioned here; however, this article focuses only on the three constants that are used for case conversion.

Demonstrating the StrConv Function

  1. Start Microsoft Access, and then open a new blank database.
  2. Create a new table with the following fields:
       Field Name: FirstName
       Data Type: Text
    
       Field Name: LastName
       Data Type: Text
    						
    Save the table as MyNamesList.
  3. Add the following sample names to the table:
       john chen
       joanna fuller
       becki culbert
       jeff smith
    					

Using StrConv in Code in the AfterUpdate Property of a Control

  1. Create a new form that is based on the MyNamesList table.
  2. Add text box controls for the FirstName and LastName fields by dragging the field names from the field list.
  3. If the property sheet is not visible, on the View menu, click Properties.
  4. Set the AfterUpdate property of the LastName text box to the following event procedure:
    Private Sub LastName_AfterUpdate()
    LastName = StrConv(LastName, vbProperCase)
    End Sub
    					
  5. On the File menu, click Close.
  6. Open the form that you created in step 1 in Form view, and enter some new names in lowercase. Note that when you return to these records, the LastName field is now correctly capitalized.

Using StrConv() in a Query

  1. Create a new query that is based on the MyNamesList table, and then type the following line in the first Field cell of the query design grid:
    FullName: StrConv([LastName] & ", " & [FirstName], 3)
  2. Run the query.

    Note that the LastName and FirstName fields are concatenated and any names beginning with lowercase are converted so that the first letter is uppercase.

Using StrConv in a Macro

  • Create a new macro called Proper with the following specifications:
    Action: SetValue
    Item: Screen.ActiveControl
    Expression: StrConv(Screen.ActiveControl,3)
    NOTE: You can call this macro from the AfterUpdate property of a control on a form. This has the same effect as the first method.

References

For more information about other constants of the StrConv function, search the Help Index for StrConv, and then click the StrConv function topic.

Applies to
  • Microsoft Access 97 Standard Edition
Keywords: 
kbhowto kbprogramming KB302499
Retired KB ArticleRetired KB Content Disclaimer
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.
Share
Additional support options
Ask The Microsoft Small Business Support Community
Contact Microsoft Small Business Support
Find Microsoft Small Business Support Certified Partner
Find a Microsoft Store For In-Person Small Business Support