Capitalize the first letter of a word in Microsoft Access 97

I am not a big fan of writing code…the stuff I struggled through at UCI more than cured me of any itch to be a code monkey.  However, by virtue of my many hats, as a DBA (Database Administrator) I sometimes have to write some Visual Basic in queries in order to correct information in records I’m dealing with.  One of the more interesting challenges I have undertaken in the past (before my blogging days) was to write code that formats people’s first or last names.

In Access 97 there is a function called StrConv() which suits this purpose well, but it doesn’t go far enough.  Some last names contain apostrophes (“O’Enright”), dashes (“Enright-Smith”) or the field that you are editing may contain both first initial and last name separated by a dot (“M.Enright”), etc. and your StrConv function won’t catch those cases. 

This is where you will need a combination of functions in order to parse the string for special characters.  In Access 97 I like to use this code to update a last name field after I have performed the initial StrConv to correct the bulk of the records:

Mid([Last Name],1,InStr(1,[Last Name],”-“))+StrConv(Mid([Last Name],InStr(1,[Last Name],”-“)+1),3) 

Reference:
Mid() Function
InStr() Function
StrConv() Function
This example corrects last names with dashes in them.  The green portion grabs the substring from the start of the name until the end of the dash.  In our example of (“Enright-Smith”)  this substring would be: (“Enright-“).  The orange portion does a StrConv on the remaining string to capitalize the first letter in the word coming after, i.e. (“Smith”).
The plus sign in between the two places the two separate strings together to form our new and formatted string.

Note:  You can run the same function, replacing “-” with dot or whatever character you choose.  Also, note that there are other more complicated functions that have been written to manipulate strings of text, but the code I provided here will deal with most scenarios.  If you have any doubts, you should check your data for other cases.

If you are working with Access 2000 you can use the Proper() function, which does both regular first letter capitalization and also parses words separated by special characters. Here is the code for that function:

'******************* Code Begin ****************
Function Proper(X)
' Capitalize first letter of every word in a field.
' Use in an event procedure in AfterUpdate of control;
' for example, [Last Name] = Proper([Last Name]).
' Names such as O'Brien and Wilson-Smythe are properly capitalized,
' but MacDonald is changed to Macdonald, and van Buren to Van Buren.
' Note: For this function to work correctly, you must specify
' Option Compare Database in the Declarations section of this module.
Dim Temp$, C$, OldC$, i As Integer
    If IsNull(X) Then
        Exit Function
    Else
        Temp$ = CStr(LCase(X))
        ' Initialize OldC$ to a single space because first
        ' letter needs to be capitalized but has no preceding letter.
        OldC$ = " "
        For i = 1 To Len(Temp$)
            C$ = Mid$(Temp$, i, 1)
            If C$ >= "a" And C$ <= "z" And _
                (OldC$ < "a" Or OldC$ > "z") Then
                    Mid$(Temp$, i, 1) = UCase$(C$)
            End If
            OldC$ = C$
        Next i
        Proper = Temp$
    End If
End Function
'******************* Code End ****************