How to convert data values of the Text data type to the proper case format in Microsoft Access

Article translations Article translations
Article ID: 815282 - View products that this article applies to.
Moderate: Requires basic macro, coding, and interoperability skills.
This article applies to a Microsoft Access database (.mdb) file or to a Microsoft Access database (.accdb) file.
Expand all | Collapse all

On This Page

SUMMARY

This article describes how to convert data values of the Text data type to the proper case format.

MORE INFORMATION

The data values of Text data type can be converted to the proper case format by using one of the following methods:
  • Method 1: Use the Built-In String Conversion Function
  • Method 2: Use a User-Defined Function
Note Before you use either of these methods, create a sample table that is named MyTestTextList. To do this, follow these steps:
  1. Create a new blank database in Access.
  2. Create a new table with the following fields:
    Field Name: testText
    Data Type: Text
    
  3. Save the table as MyTestTextList.
  4. Add the following sample testText to the table:
    the cOw jumped Over the MOON
    
    THE QUICK BROWN FOX jUmped over THE lazy DOG 
    
  5. Save the database as MyTestDatabase.

Method 1: Use the built-in string conversion function

  1. Open the MyTestDatabase database in Access.
  2. On the Insert menu, click Query.

    Note In Microsoft Office Access 2007, click Query Design in the Other group on the Create tab.
  3. In the New Query dialog box, click Design view.

    Note In Access 2007, skip this step.
  4. In the Show Table dialog box, click Close.
  5. On the View menu, click SQL View.

    Note In Access 2007, click SQL in the Results group on the Design tab.
  6. Type the following code in the SQL view:
    SELECT testText, STRCONV(testText,3) as  TestText_in_Proper_Case FROM MyTestTextList
  7. On the Query menu, click Run.

    Note In Access 2007, click Run in the Results group on the Design tab.

    The output of the query follows:
    TestText					TestText_in_Proper_Case
    the cOw jumped Over the MOON The Cow Jumped Over The Moon
    THE QUICK BROWN FOX jUmped over THE lazy DOG The Quick Brown Fox Jumped Over The Lazy Dog
Note The StrConv(<Text>,3) method converts the first letter of every word in the text to uppercase. This behavior occurs only when the words are separated by a space or a tab. StrConv does not treat the special characters, such as - or $, as a word separator.

Method 2: Use a user-defined function

  1. Open the MyTestDatabase database in Access.
  2. On the Insert menu, click Module.

    Note In Access 2007, , click the drop-down arrow under Macro in the Other group on the Create tab.
  3. Type the following code in the current module and save your changes.
    Function Proper(X)
    Capitalize first letter of every word in a field.
    
    
    Dim Temp$, C$, OldC$, i As Integer
    
         If IsNull(X) Then
    
               Exit Function
    
         Else
    
               Temp$ = CStr(LCase(X))
    
               ' Initialize OldC$ to a single space because first
               ' letter must be capitalized but has no preceding letter.
    
               OldC$ = " "
    
                For i = 1 To Len(Temp$)
                       C$ = Mid$(Temp$, i, 1)
                       If C$ >= "a" And C$ <= "z" And (OldC$ < "a" Or OldC$ > "z") Then
                             Mid$(Temp$, i, 1) = UCase$(C$)
                       End If
                       OldC$ = C$
                Next i
    
                Proper = Temp$
    
          End If
    
    End Function
    
    Note You must specify Option Compare Database in the "Declarations" section of this module for the function to work correctly.
  4. On the File menu, click Close and Return to Microsoft Access.

    Note On the File menu, click Close for Access 97.
  5. On the Insert menu, click Query.

    Note In Access 2007, click Query Design in the Other group on the Create tab.
  6. In the New Query dialog box, click Design view.

    Note In Access 2007, skip this step.
  7. In the Show Table dialog box, click Close.
  8. On the View menu, click SQL View.

    Note In Access 2007, click SQL in the Results group on the Design tab.
  9. Type the following code in the SQL view:

    SELECT testText, proper(testText) as  testText_in_Proper_Case FROM MyTestTextList
    Notice that this query is similar to the query in Method 1. This is except for the function call.
  10. On the Query menu, click Run.

    Note In Access 2007, click Run in the Results group on the Design tab.

    The output of the query follows:
    TestText					TestText_in_Proper_Case
    the cOw jumped Over the MOON The Cow Jumped Over The Moon
    THE QUICK BROWN FOX jUmped over THE lazy DOG The Quick Brown Fox Jumped Over The Lazy Dog
While the output of both methods is similar, Method 2 gives you the flexibility to select any case format. This includes a chosen word separator such as - or _. You can define the required case format, or you can define a word separator. You can do this if you modify the Proper function that is mentioned in step 3.

REFERENCES

For additional information, click the following article numbers to view the articles in the Microsoft Knowledge Base:
298607 ACC2002: How to Use the StrConv Function to Capitalize Words and Phrases
253911 ACC2000: How to Use the StrConv Function to Capitalize Words and Phrases
302499 ACC97: How to Use the StrConv Function to Capitalize Words and Phrases
209903 ACC2000: Cannot Use Intrinsic Constants in Queries, Forms, Reports

Properties

Article ID: 815282 - Last Review: April 11, 2007 - Revision: 5.1
APPLIES TO
  • Microsoft Office Access 2007
  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
  • Microsoft Access 2000 Standard Edition
  • Microsoft Access 97 Standard Edition
Keywords: 
kbinfo kbexpertiseinter kbprogramming kbconversion kbhowto KB815282

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com