Proper Case Code

Question

Does anyone have the VBA code to automatically format names in an Access table including names such as  Ian McPherson, John MacDonald, Michael O'Neil etc.?

Answer 1

Here is a function I created. It doesn't correct names with "Mac" because there are too many names that start with Mac that shouldn't force a cap after it.

Public Function PropCaps(varLastName) As Variant

'Purpose  : Proper capitalization of names with more than one Cap

'           such as D'Angelo, O'Brein, McDonald.

'Note     : does not attempt names like MacDoogal. Too many exceptions

'           with names starting with Mac.

'DateTime : 5/05/2000

'Author   : Bill Mosca

    Dim varOut As Variant

    Dim intPos As Integer

    If IsNull(varLastName) Then Exit Function

    'Irish

    intPos = InStr(1, varLastName, "MC", vbTextCompare)

    If intPos > 0 Then

        varOut = StrConv(Left(varLastName, 2), vbProperCase) _

            & StrConv(Mid(varLastName, 3), vbProperCase)

    End If

    'Various ancestry.

    intPos = InStr(varLastName, "'")

    If intPos > 0 Then

        varOut = StrConv(Left(varLastName, intPos), vbProperCase) _

            & StrConv(Mid(varLastName, intPos + 1), vbProperCase)

    End If

    PropCaps = varOut

End Function

Answer 2

Hi Heart, I'm an independent adviser and can help.

StrConv(String, vbProperCase)

However, that will not handle some of the examples you gave. It will only capitalize the first letter of full words. So john macdonald will come up as John Macdonald. There is no way I know of to cover those odd situations except to eyeball. You can try filtering for Mac and Mc and other examples.

Answer 3

Hi Heart

Here is the code you are looking for to convert string values into Proper Case

Make sure to read the 'Advanced Capitalization' section . . .

https://www.fmsinc.com/MicrosoftAccess/query/ac...

________________________________________________________

Standard Disclaimer: This is a non-Microsoft website. The page appears to be providing accurate, safe information. Watch out for ads on the site that may advertise products frequently classified as a PUP (Potentially Unwanted Products). Thoroughly research any product advertised on the site before you decide to download and install it.

.