MS-Access / Getting Started

Looking at how PCase() works

Before we 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 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 acts as a placeholder for text being operated on by the function. The Dim statement just declares the variable as a string. The second statement stores a copy of AnyText, already converted to the 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. For a list of other available constants for the StrConv() function and how to use them, highlight the word StrConv in the code and then press F1 to open the Help feature for that function.

Back in 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, leave the first two characters of FixedText unchanged, followed by changing the third letter to uppercase, followed by all the rest unchanged."

'If first two letters are "Mc", cap third letter.
If Left(FixedText, 2) = "Mc" Then
    FixedText = Left(FixedText, 2) & _
	UCase(Mid(FixedText, 3, 1)) & Mid(FixedText, 4)
End If

Because FixedText at this moment 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, and then leave 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) & _
	UCase(Mid(FixedText, 4, 1)) & Mid(FixedText, 5)
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 executes. 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 Function statement then ends the function and returns the contents of PCase (MacDonald) to the code (or object) that called the function:

    PCase = FixedText
End Function

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..

[Previous] [Contents] [Next]