How to Convert Text to Title Case for Movies, Books, and Songs in Microsoft Access

 

Today's TechHelp tutorial from Access Learning Zone covers the topic of converting text to title case, where the appropriate words in titles like movies, books, and songs are capitalized. Landon from Mount Prospect, Illinois, asked how to change all the text in his database so that the first letter of each word is capitalized, as seen in titles. The text in his Microsoft Access database consists of inconsistent cases, and he seeks an efficient method to standardize them without manual edits.

To address Landon's query, I have previously discussed changing the case of letters and words. In my Change Case video, we explored converting text to uppercase, lowercase, and proper case using UCase, LCase, and a custom proper case function. Proper case converts the first letter of each word to a capital letter while making the remaining letters lowercase. However, for titles, not every word needs to be capitalized—articles and short conjunctions like "the," "of," "a," and others typically remain in lowercase unless they appear at the beginning of the title.

We're going to modify the proper case function to accommodate these special rules. This tutorial assumes a developer-level understanding, including familiarity with Dlookup, NZ function, for-next loops, and arrays. You should also review videos on the split function since we'll be using it to manage arrays.

First, create a "movie" table in Access to store the list of titles, ensuring none of them use field names like "name" or "date," which are reserved words. Populate this table with movie titles, either in lowercase or varying cases. Tools like ChatGPT can generate random titles for practice.

Once you have your list, create a query to convert these titles to proper case using the StringConvert function with a parameter of 3 for VB proper case. Note that while VB proper case works in VBA, it needs numerical representation in queries.

To enhance this function, we'll create our own VBA function called title case. This function will convert the entire string to proper case first and then determine which words should revert to lowercase based on a predefined list. Instead of hardcoding this list into VBA, we'll store it in a separate "small word" table in Access. This approach allows users to easily update the list as needed.

Next, split the input string into an array of words using the split function, so each word can be processed individually. Loop through the array, checking each word against our list of small words. If a match is found and the word is not the first in the title, it is converted back to lowercase.

By the end of this part of the tutorial, you'll have the groundwork for your title case function, which can handle converting text to title case, considering specific capitalization rules. Tomorrow, we'll continue refining this function, focusing on rebuilding and returning the string. 

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. 

Live long and prosper, my friends.

For a complete video tutorial on this topic, please visit https://599cd.com/TitleCase?key=Blogger