How to convert data values of the text data type to proper case format in Access?

Read time 6 minutes

In Microsoft Access, the default data type is the TEXT data type. 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 the MS Access database, then this article would be of great help. This article describes steps involved in converting data values of the Text data type to the proper case format in MS Access.

There are two ways with which you can convert data values of the 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 methods, first of all, create a sample table named MyTestTextList in the new blank database MyTestDatabase.

Use the Built-In String Conversion Function

Open the MyTestDatabase database in Access.
On the Insert menu, click Query.
In MS Office Access 2007, click Query Design in the Other group on the Create tab
In the New Query dialog box, click Design view.
In Access 2007, skip this step.
In the Show Table dialog box, click Close.
On the View menu, click SQL View.
In Access 2007, click SQL in the Results group on the Design tab.
Type the code in the SQL view

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

On the Query menu, click Run.

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

Use a user-defined function

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

In Access 2007, click the drop-down arrow under Macro in the Other group on the Create tab.

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

On the File menu, click Close and Return to Microsoft Access.
On the Insert menu, click Query. In Access 2007, click Query Design in the Other group on the Create tab
In the New Query dialog box, click Design view. In Access 2007, skip this step.
In the Show Table dialog box, click Close.
On the View menu, click SQL View. In Access 2007, click SQL in the Results group on the Design tab.
Type the following code in the SQL view

SELECT testText, proper(testText) as testText_in_Proper_Case FROM MyTestTextList

This query is similar to the query in Method 1. This is except for the function call.

On the Query menu, click Run. In Access 2007, click Run in the Results group on the Design tab

The second method gives you the flexibility to select any case format. This way you are able to convert data values of the Text data type to proper case format in MS Access.

An automated Access repair tool helps can help here to restore corrupt MDB databases to working databases. Kernel for Access Database Repair tool recovers tables, key data structures, auto number, table relationships, OLE data, memo data with original date formats. It repairs all corruption-related issues with MDB and ACCDB files and retrieves exact content from the database file with the help of repair modes. Users can preview the content before saving and repairs database files from any MS Access database version.

Free Download 100% Secure

Here is provided the working process of the Kernel for Access Database Repair tool to recover and restore corrupt MDB database to working database.

Launch the installed Kernel for Access Repair on your system.
Click ‘Select File’ to browse and add the corrupted Access database files.
Select the Mode to repair the corrupt Access file. Then click Repair.
Wait until the scan and repair process gets completed.
Once the process gets completed, you can preview the recovered file by selecting the objects from the left panel. Click Save to save the required file.
Provide a destination path to save the recovered file and click Save.
The repaired database file will be saved at the specified location.

Hence the easy restoration of the corrupted MDB file is completed conveniently.

Wrapping Up

In this blog, two manual ways are explained to convert the Text data type to the proper case in Microsoft Access – using built-in string conversion function and user-defined function. It also helped those users who encounter issues due to corrupt Access databases with the best professional Access database repair solution.

Free Download 100% Secure

How to open MDB file for free?

How to repair corrupt Microsoft Access database?

How to fix Access database error 3151?