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

In Microsoft Access, the default data type is TEXT. However, one can change the text data type to another desired data type. Choosing a specific data type is totally one’s own perception and his/her choice as doing so ensures that you will get the best results from your queries and searches. If you are using MS Access database, then this article would be of great help. This article describes steps involved in converting data values of Text data type to proper case format.

There are two ways using which you can convert data values of Text data type to proper case format.
Use the Built-In String Conversion Function
Use a User-Defined Function
Before going for any one of the above method, first of all create a sample table named MyTestTextList in new blank database MyTestDatabase.
Use the Built-In String Conversion Function
1.Open the MyTestDatabase database in Access.
2.On the Insert menu, click Query. In MS Office Access 2007, click Query Design in the Other group on the Create tab.
3.In the New Query dialog box, click Design view. In Access 2007, skip this step.
4.In the Show Table dialog box, click Close.
5.On the View menu, click SQL View. 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. In Access 2007, click Run in the Results group on the Design tab.
Use a user-defined function
1.Open the MyTestDatabase database in Access.
2.On the Insert menu, click Module. 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
4.On the File menu, click Close and Return to Microsoft Access.
5.On the Insert menu, click Query. In Access 2007, click Query Design in the Other group on the Create tab.
6.In the New Query dialog box, click Design view. In Access 2007, skip this step.
7.In the Show Table dialog box, click Close.
8.On the View menu, click SQL View. In Access 2007, click SQL in the Results group on the Design tab.
9.Type the following code in the SQL view:
10.SELECT testText, proper(testText) as testText_in_Proper_Case FROM MyTestTextList
11.This query is similar to the query in Method 1. This is except for the function call.
12.On the Query menu, click Run. In Access 2007, click Run in the Results group on the Design tab
Second method gives you the flexibility to select any case format. This way you are able to convert data values of Text data type to proper case format in MS Access.

Comments are closed.