Access Hacks

Hack #89. Convert Text to the Desired Case

Have any text string be returned in uppercase, lowercase, or proper case.

One of the occasional requirements thrown at a developer is the ability to change the case of the text. This isn't a really difficult problem. All you have to do is use the UCase or LCase functions, which return a text string as all capital letters or all lowercase letters, respectively.

However, no function is available for returning proper case (a.k.a. mixed case or sentence case): text in which each word starts with an uppercase letter, with the rest of the word in lowercase.

Microsoft Word has the ability to return proper case, but Access doesn't. While you're waiting for the two development teams at Microsoft to get together on this, here is a function that returns all three case types: upper, lower, and proper. The function takes two arguments: the text to be converted and the type of treatment to apply.

The Code

When converting to upper- or lowercase, the function simply uses the respective built-in UCase or LCase function. Why reinvent the wheel?

To convert text to proper case requires a looping process. If you think about it, all you need to do is apply UCase or LCase to each character in the text. The trick is to know which letters get which treatment.

Function change_case(txt As String, case_type As String) As String Select Case case_type Case "Upper" change_case = UCase(txt) Case "Lower" change_case = LCase(txt) Case "Proper" 'create proper case Dim space_flag ...