## 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). 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 retype all the data manually.

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

To get around that, you could 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 capitalized), just like the StrConv() function does.

But your custom function could then use some If...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.coolnerds.com/vba 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 (I call this one PCase()) and create an appropriate function. In this case, you need to pass a string (which I refer to as AnyText) to the function. The return value for the function will also be a string (whatever text was passed converted to initial caps). Listing 11-1 shows the function in its entirety. I 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

End If

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

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

How PCase() works

Before I talk about using the PCase() function, take a moment to see how it works. PCase() uses several built-in Access functions — StrConv(), Left(), UCase(), and Mid() — to work with chunks of text in the passed string. For the sake of example, see what happens when PCase() gets called with something like PCase("MACDONALD"):

When PCase() is called in this example, AnyText becomes a string variable that contains the text MACDONALD. The AnyText argument is defined as a string in the Function() statement itself, as shown here:

Public Function PCase(AnyText As String) As String

The next two statements declare a new string variable named FixedText, which act as a placeholder for text being operated upon by the function. The Dim statement just declares the variable as a string. The second statement stores a copy of AnyText, already converted to proper case by using the StrConv() method:

Dim FixedText As String

FixedText = StrConv(AnyText, vbProperCase)

In VBA, you can use constants (like vbProperCase) rather than numbers

(like 3) in built-in functions. Thus, StrConv(AnyText, vbProperCase) is the same as= StrConv(AnyText, 3).

Going back to the example of calling the function, by the time the two preceding statements have been executed, the FixedText variable contains Macdonald. That's close to what you need, but the function isn't done working yet.

The next statements say, If the first two letters of FixedText are Mc, change FixedText to the first two characters of FixedText unchanged, followed by the third letter in uppercase, followed by all the rest unchanged.

'If first two letters are "Mc", cap If Left(FixedText, 2) = "Mc" Then FixedText = Left(FixedText, 2) + UCase(Mid(FixedText, 3, 1)) +

End If third letter. Mid(FixedText, 4)

Because FixedText at this moment in time contains Macdonald, this block of code is ignored because its first two letters are ma, not mc. By the time the preceding statements execute (in this example), FixedText still contains Macdonald. Nothing has changed there.

The following block of code says, If the first three characters are mac, change FixedText to the first three letters of itself, followed by the fourth letter in uppercase, then the rest of the string unchanged.

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

End If

In the current example, FixedText contains Macdonald when code execution reaches the If statement. And the first three letters of FixedText are indeed mac; thus, the code inside the If...End If block will execute. In doing so, it changes FixedText to its own first three letters unchanged (Mac), plus the fourth letter in uppercase (D), plus the rest of the string, unchanged (onald). By the time execution gets past the End If statement in this example, FixedText contains MacDonald.

The following block of code does basically the same thing as the two preceding blocks. It looks to see whether the first four letters of the string are P.o. — and if so, changes those first four letters to P.O. Of course, the first four letters of MacDonald aren't P.O., so that whole block of code is skipped over.

These final statements assign the current contents of the FixedText variable (MacDonald, now) to the function name sans parentheses (PCase). The End Sub statement then ends the function and returns the contents of PCase (MacDonald) to the code (or object) that called the function.

PCase = FixedText End Sub

If you type ? PCase("macdonald") into the Immediate window, it returns MacDonald. If you type ? PCase("P.O. BOX 123") into the Immediate window, you get P.O. Box 123. If you type ? PCase("HELLO WORLD") into the Immediate window, you get Hello World. The StrConv() function inside PCase() still does its thing. The If...End If statement just makes minor corrections for Mc, Mac, and P.O..

0 0