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.
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(String, Conversion as VbStrConv, [LocalID])
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
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:
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:
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.
The examples above are in this sample database so you can try it yourself:
Sample Database (139K)