databasedev.co.uk - database solutions and downloads for microsoft access

Convert Text to Proper Case or Upper Case in Microsoft Access

When users are entering data into your Microsoft Access database, there may be times where you wish to view the information in the Microsoft Access forms, or store the information in your database tables, using a particular text case format.

Microsoft Access contains functions that will convert input in to Proper Case, Upper Case and Lower Case, and the first two are discussed here.

vbProperCase - Converts the first letter of every word in string to uppercase.

vbUpperCase - Converts the string to uppercase characters.

How to implement these in your Microsoft Access database

There are many ways available to add this functionality and one way is to create the functions in a module and call them in the After Update event of a text box on your forms. To add the code to a module you can use the following examples:

'----------------------------
'Converts Text to Proper Case
'----------------------------
Public Sub ConvertToProper()
'Test if control contains text
    If IsNull(Screen.ActiveControl) = False Then
'convert text to Proper Case
        Screen.ActiveControl = StrConv(Screen.ActiveControl, vbProperCase)
    End If
End Sub


'----------------------------
'Converts Text to Upper Case
'----------------------------
Public Sub ConvertToUpper()
'Test if control contains text
    If IsNull(Screen.ActiveControl) = False Then
'convert text to Proper Case
        Screen.ActiveControl = StrConv(Screen.ActiveControl, vbUpperCase)
    End If
End Sub

Now to call these add the following to the After Update event procedure of any textbox that you want to ensure the format of the data:

Private Sub strStudentLastName_AfterUpdate()
'Convert the text entered to Proper Case
    ConvertToProper
End Sub


Private Sub strStudentNumber_AfterUpdate()
'Convert the text entered to Upper Case
    ConvertToUpper
End Sub

You can now view and store the information in the desired format. Use the same approach to implement the vbLowerCase function.