## Proper Case Function

Take a look now at a somewhat larger custom function that does more than a simple match calculation. Suppose you have a table filled with names and addresses, but for whatever reason, all the text is in uppercase (or lowercase) letters. For example, maybe the table has a Name field containing names like JOE SMITH or joe Smith. You want to tidy that up, but you certainly don't want to go in and edit all the data manually.

Technically, you could just use the built-in StrConv( string, vbProperCase) function to solve this problem. For example, StrConv("JOE SMITH", vbProperCase) returns Joe Smith. Problem solved — except that StrConv() doesn't take into consideration little quirks like the uppercase D in McDonald. StrConv("MCDONALD", vbProperCase) returns Mcdonald. Likewise, StrConv("p.o. box 123", vbProperCase) returns P.o. Box 123, which doesn't look quite right because the O should be uppercase.

To get around that, you can create your own, custom function that takes any string as its argument and then returns that string with initial caps (the first letter of each word is capitalized), just like the StrConv() function does. But your custom function can then use some lf...End If statements to correct any little problems, like the Mcdonald and P.o. Box examples.

You don't really have to type any of the functions shown in this book into your own database. You can download them from www.dummies.com/go/ access2007vbaprog and just import them into a database.

You might want to use this function to fix several fields in several tables, so you want the function to be public, like any built-in function. For starters, you need to open or create a standard module. Think up a name for your function (we call this one PCase()) and create an appropriate function. In this case, you need to pass a string (which we refer to as AnyText) to the function. The return value for the function is also a string (whatever text was passed is converted to initial caps). Listing 11-1 shows the function in its entirety. We take a look at how it works in a moment.

Listing 11-1: Sample PCase() Custom Function

'The PCase() function accepts any string, and returns 'a string with words converted to initial caps (proper case). Public Function PCase(AnyText As String) As String

'Create a string variable, then store AnyText in that variable already 'converted to proper case using the built-in StrConv() function Dim FixedText As String

FixedText = StrConv(AnyText, vbProperCase)

'Now, take care of StrConv() shortcomings

'If first two letters are "Mc", cap third letter. If Left(FixedText, 2) = "Mc" Then

UCase(Mid(FixedText, 3, 1)) & Mid(FixedText, 4)

End If

'If first three letters are "Mac", cap fourth letter. If Left(FixedText, 3) = "Mac" Then

UCase(Mid(FixedText, 4, 1, )) & Mid(FixedText, 5)

End If

'If first four characters are P.o. then cap the "O". If Left(FixedText, 4) = "P.o." Then

FixedText = "P.O." & Mid(FixedText, 5) End If

'Now return the modified string. PCase = FixedText End Function