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."
function can be used to change the case of a string to uppercase, lowercase, or so that the first letter is uppercase. The syntax is StrConv
, 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, instead of the integers 1, 2, or 3, you can also use one of the following constants:
vbUpperCase Converts the entire string to uppercase.NOTE:
vbLowerCase Converts the entire string to lowercase.
vbProperCase Converts the first letter of each word to uppercase and the remaining letters to lowercase.
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
- Start Microsoft Access, and then open a new blank database.
- Create a new table with the following fields:
Save the table as MyNamesList.
Field Name: FirstName
Data Type: Text
Field Name: LastName
Data Type: Text
Add the following sample names to the table:
Using StrConv in Code in the AfterUpdate Property of a Control
- Create a new form based on the MyNamesList table.
- Add text box controls for the FirstName and LastName fields by dragging the field names from the Field List box.
- If the property sheet is not visible, click Properties on the View menu.
- Set the AfterUpdate property of the LastName text box to the following event procedure:
Private Sub LastName_AfterUpdate()
LastName = StrConv(LastName, vbProperCase)
- On the File menu, click Close and Return to Microsoft Access.
- 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 names are now correctly capitalized.
Using StrConv in a Query
- Create a new query 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)
- Run the query.
The last names and first names are concatenated and any names beginning with lowercase are converted so that the first letter is uppercase.
Using StrConv in a Macro
For more information about other constants of StrConv function, in the Visual Basic Editor, click Microsoft Visual Basic Help
on the Help
menu, type strconv
in the Office Assistant or the Answer Wizard, and then click Search
to view the topic.