a.. Method 1: Use the Built-In String Conversion Function
b.. 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.. Run 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_Casethe cOw jumped Over the MOON The Cow
Jumped Over The MoonTHE 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 IfEnd
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_Casethe cOw jumped Over the MOON The Cow
Jumped Over The MoonTHE 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.
