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

Using an Update Query to Convert Text to Proper Case

Occasionally you will want to work with text of a particular case, and the text that has been entered into the database table may not be the text that you want to use. If the text has been entered incorrectly to begin with, this may reflect when you are viewing the data in your forms or reports.

We can update the data in the table by using a Microsoft Access string conversion function, called StrConv(). The String Conversion function includes arguments that can be used to convert text to:

• UpperCase
• LowerCase
• ProperCase

The conversion argument settings are:

Constant	Value	Description
vbUpperCase	1	Converts the string to uppercase characters.
vbLowerCase	2	Converts the string to lowercase characters.
vbProperCase	3	Converts the first letter of every word in string to uppercase.

The string argument that we use can be a literal string, for example "tERRY jONES", or it is more likely that we would use the field that contains the text we want to convert, for example [EmployeeFullName].

If we look at the following table, we will see that there has been problems with the data entry, and we wouldn't want to see this data appearing as is when we run reports.

Data before running the strConv function

To enable us to update the data in one go, rather than having to manually update each record, we can run an Update Query. If we the strConv function in here, we can convert all of the data to our chosen format.

The Update Query design should look like the following:

The Update Query, including the strConv function

You will see that we have used the strConv function in the query design, however, we can't use the vbProperCase constant. Instead, we must use the numeric value that the constant represents - 3 (in this case as we are doing a ProperCase conversion).

The SQL for this would look like the following:

UPDATE tblEmployeeFullNames
SET tblEmployeeFullNames.EmployeeFullName = StrConv([EmployeeFullName],3);

When we run the Update Query, the data will be converted and will return the following results:

The data after the strConv function has been run to convert the data to Proper Case