Convert a Text data type to proper case format (815282)

MORE INFORMATION

The data values of Text data types can be converted to 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:

Create a Sample Table MyTestTextList

1. Run Access. Open a new, blank database.
2. Save the database as MyTestDatabase.
3. Create a new table with the following fields:

   Field Name: testText
   Data Type: Text
   

4. Save the table as MyTestTextList.
5. Add the following sample testText to the table:

   the cOw jumped Over the MOON
   
   THE QUICK BROWN FOX jUmped over THE lazy DOG 
   

Method 1
Use the Built-In String Conversion Function

1. Start Access. Open MyTestDatabase database.
2. On the Insert menu, click Query.
3. In the New Query dialog box, click Design view.
4. In the Show Table dialog box, click Close.
5. On the View menu, click SQL View.
6. Type the following code in the Microsoft Access -[Query..] dialog box:

   SELECT testText, STRCONV(testText,3) as  TestText_in_Proper_Case FROM MyTestTextList

7. On the Query menu, click Run.
   
   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.

For more information about valid word separators for proper casing in the 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.

Method 2
Use a User-Defined Function

1. Run Access and then open MyTestDatabase database.
2. On the Insert menu, click Module.
3. Type the following code in the current module. 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.
6. In the New Query dialog box, click Design view.
7. In the Show Table dialog box, click Close.
8. On the View menu, click SQL View.
9. Type the following code in the Microsoft Access -[Query..] dialog box:
   
   

   SELECT testText, proper(testText) as  testText_in_Proper_Case FROM MyTestTextList

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