Hack 89. Convert Text to the Desired 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.
8.19.1. 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 As Boolean space_flag = False 'first letter is alway uppercase change_case = UCase(Left(txt, 1)) For test_case = 2 To Len(txt) If Mid(txt, test_case, 1) = " " Then space_flag = True change_case = change_case & LCase(Mid(txt, test_case, 1)) Else If space_flag = True Then change_case = change_case & UCase(Mid(txt, test_case, 1)) space_flag = False Else change_case = change_case & LCase(Mid(txt, test_case, 1)) End If End If Next test_case End Select End Function
To start, the first letter of the string becomes uppercase. That one is a given. Then, a loop cycles through the rest of the text string. A character can be a space. When a space is encountered, a flag is set to true. When a nonspace character is encountered, one of two things can happen:
Note that you don't need to test whether a character is upper-or lowercase while it is being evaluated. If it follows a space, it ends up as uppercase, regardless of the case in which it was typed. The same approach holds true for characters that don't follow a space: they are set to lowercase regardless.
8.19.2. Running the Code
The change_case function needs two arguments. You can specify them from field controls, from code, or even from within the Immediate window. Figure 8-32 shows how the function is called from a form.
A text box contains the string of text, a listbox offers the three case types, and a command button calls the function with this little snippet of code:
Private Sub Command2_Click() Dim z As String z = change_case(Me.Text1, Me.List1) MsgBox z End Sub
Figure 8-32. Returning text in a selected case