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.

Table with Field in ALL CAPS

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:

Update Query with StrConv to Change to Proper Case

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:

Field Updated to Proper Case by Query using StrConv

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:

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.

Sample Database

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 Analyzer 2016
See What's Really Going on Inside Your Access Databases!

Microsoft Access 2016 Database Documenter


Total Access Emailer 2016
Send Personalized Emails from Microsoft Access!

Microsoft Access Email Blaster


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