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

Provided by: Luke Chung, FMS President

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).

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.

Table with Field in ALL CAPS

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)

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

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:

Update Query with StrConv to Change to Proper Case

In our example, we're using:


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:

Field Updated to Proper Case by Query using StrConv

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:

Advanced Proper Case Conversion

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.

The examples above are in this sample database so you can try it yourself:

Sample Database Sample Database (139K)

Total Zip Code DatabaseAdditional Resources

Total Access Emailer 2022
Email Everyone in Your Access List!

Email personalized messages with attached reports from Microsoft Access

Total Visual Agent 2021
Automate Access Database Chores!

Automate Microsoft Access database compact and other chores

Microsoft Access to SQL Server Upsizing

Updated Microsoft Access to SQL Server Upsizing Center with whitepapers, resources, and SQL Server Express Downloads

Free Product Catalog from FMS