Microsoft Access Query Tip to Convert a Text Field to Proper Case

Provided by: Luke Chung, FMS President

Issue

A field contains text that's all capital letters, all lower case, or a mix. You'd like to have the data in proper case (first letter in each word capitalized, other letters in lower case).

Example

Our Total ZipCode Database provides data from the US Postal Service for each zip code. The City and State names are provided in ALL CAPS, but most people want it in Proper Case.

VBA Function StrConv

A VBA function string conversion function, StrConv, lets you modify text to Proper Case, Upper Case or Lower Case.

The StrConv function has two parameters. The first is the string to be converted and the second is how to convert it. It can be converted.

StrConv Syntax

StrConv(String, Conversion as VbStrConv, [LocalID])

String: The string to convert
Conversion Type:
- vbProperCase for proper case (value = 3)
- vbUpperCase for ALL CAPS (value = 1)
- vbLowerCase for all lower case (value = 2)
- There are other values for Unicode, Japanese, etc. that are not relevant here
Optional LocalID for language specific values

From the Immediate Window [Ctrl G], you can use the StrConv function to see what values it generates:

? StrConv("uNiTed sTatEs", vbProperCase)
United States

? StrConv("uNiTed sTatEs", vbUpperCase)
UNITED STATES

? StrConv("uNiTed sTatEs", vbLowerCase)
united states

Update Query

By using StrConv in an Update Query, one can convert the value of a field into Proper Case and update a field with it. One can replace the original field or in this case, update another field:

In our example, we're using:

StrConv([CityCAPS],3)

Note that we have to use the value 3 rather than the VBA constant name vbProperCase because VBA constants are not supported in queries. If you want to convert to all upper case or all lower case, use 1 and 2 respectively.

When that query is run, the [CityProper] field is updated:

Advanced Capitalization

For more sophisticated processing, words like "of", "and", "the" after the first word are not capitalized, some abbreviations like AFB are capitalized, and some letters in a word such as after "Mc" in "McLean" are capitalized, while a word like d' is not.

Here's an example of some differences between the StrConv proper case conversion and a more advanced coversion:

We used advanced proper case conversion in our Total Zip Code Database. It requires VBA code to parse words, a list of words to capitalize or not, and more sophisticated programming outside the scope of this tip. If you need help with this, our Professional Solutions Group can help.