Dealing with Names Mc* and Mac* (1 Viewer)

craigachan · May 22, 2008

I have a database with patient names. All the patient information is in capital letters. This drives the doctors nuts when letters are written as addresses and patient names are all uppercase.

For the most part I can StrConv() all the information. But can someone tell me how to deal with the McHenrys or MacDonalds. How can I capitalize the third and forth character? Or for that mater any other name like "DuPont". The user input is always in uppercase (some brite idea someone had.) I think it would be easy if there were only the "Mc's" but there are also other names to deal with.

Thanks for any help.

DCrake · May 22, 2008

Simple Software Solutions

The only sure fire way to get around this problem is to either have an exception list that replaces the strings automatically. Or have an InputBox that activates on the click of a button/icon next to the surname field that defaults the StrConv() and do a manual override of the name. So when you save the record it saves it as is.

CodeMaster:

KenHigg · May 22, 2008

I'm thinking the list is about the only way to do this as well. There are simply too many variations. You could code for Mc & Mac, but the rest would go in the table...

John.Woody · May 22, 2008

Here's a function written by someone called David McAfee, so I guess it drove him wild!! Anyway it works really well, you just call it on the after update of the txt control preceeded by an

Code:

On Error Resume Next

to allow for null

Code:

Function fProperCase(AnyText As String) As String
'Convert passed text to all lowercase. Use ProperCase()
'as you would a built-in Access function.

'If any improvement are made to this code, please
'email David McAfee at mcafee_audio@msn.com

If Nz(AnyText, "") = "" Then Exit Function 'If passed value is a null, ignore all the stuff below.
Dim intCounter As Integer
Dim OneChar As String
Dim StartingNumber As Integer
StartingNumber = 1

If Right(Left(AnyText, 4), 1) = " " Then 'Check for 3 letter words
'    If MsgBox("In the example '" & AnyText & "', shall I capitalize the three letter word '" & Mid$(AnyText, intCounter + 1, 3) & "' to '" & UCase(Mid$(AnyText, intCounter + 1, 3)) & "' ?", vbYesNo, "Three letter word was found") = vbYes Then
        'Yes was selected, so Capitalize the 3 char's
'        AnyText = UCase(Left$(AnyText, 3)) & LCase$(Mid$(AnyText, 4, 255))
'        StartingNumber = 4
'    Else
'       'No was selected, so only capitalize the first of the 3 char's
       AnyText = UCase$(Left$(AnyText, 1)) & LCase$(Mid$(AnyText, 2, 255))
       StartingNumber = 2
 '   End If
ElseIf Right(Left(AnyText, 3), 1) = " " Then
'    If MsgBox("In the example '" & AnyText & "', shall I capitalize the two letter word '" & Mid$(AnyText, intCounter + 1, 2) & "' to '" & UCase(Mid$(AnyText, intCounter + 1, 2)) & "' ?", vbYesNo, "Two letter word was found") = vbYes Then
        'Yes was selected, so Capitalize the 2 char's
'        AnyText = UCase(Left$(AnyText, 2)) & LCase$(Mid$(AnyText, 3, 255))
'        StartingNumber = 3
'    Else
'        'No was selected, so only capitalize the first of the 2 char's
        AnyText = UCase(Left$(AnyText, 1)) & LCase$(Mid$(AnyText, 2, 255))
        StartingNumber = 2
'    End If
Else
    'First convert to initial cap, followed by all lowercase.
    AnyText = UCase$(Left$(AnyText, 1)) & LCase$(Mid$(AnyText, 2, 255))
    StartingNumber = 2
End If

'Look at each character, starting at the second character.
For intCounter = StartingNumber To Len(AnyText)
    OneChar = Mid$(AnyText, intCounter, 1)
    Select Case OneChar
    
        '...convert the character after dash/hyphen/slash/period/ampersand to uppercase.
        ' Such as A.B.C. Industries, B&B Mfg
        Case "-", "/", ".", "&", "("
        AnyText = Left$(AnyText, intCounter) & UCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText, intCounter + 2, 255)
        Case "'" 'Check the character two places after the apostrophe.
            If Mid$(AnyText, intCounter + 2, 1) <> " " Then 'If it is not a space, then capatilize (O'Conner, O'Niel)
                AnyText = Left$(AnyText, intCounter) & UCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText, intCounter + 2, 255)
            Else
                'Do nothing (Don't , Tom's, haven't)
            End If
        Case "c" ' Take care of the McAfee's, McDonalds & McLaughlins and such
            If (Mid$(AnyText, intCounter - 1, 1) = "M") Then 'Check if Prev Char is an M
                If ((intCounter - 2) < 1) Then 'Check to see if the M was the first character
                    AnyText = Left$(AnyText, intCounter) & UCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText, intCounter + 2, 255)
                ElseIf (Mid$(AnyText, intCounter - 2, 1) = " ") Then 'If M wasn't first character, then check to see if a space was before the M
                    AnyText = Left$(AnyText, intCounter) & UCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText, intCounter + 2, 255)
                End If
            End If
        Case " "
            Select Case Mid$(AnyText, intCounter + 1, 2)
               'Case "de" 'Add any other exceptions here Example: Oscar de La Hoya
               '     AnyText = Left$(AnyText, intCounter) & LCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText, intCounter + 2, 255)
               Case Else ' Example: A B C Manufacturing
                  AnyText = Left$(AnyText, intCounter) & UCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText, intCounter + 2, 255)
            End Select
            'If Mid$(AnyText, intCounter + 4, 1) = " " Or ((intCounter + 3) = Len(AnyText)) Then 'Check for 3 letter words
            '    If MsgBox("In the example '" & AnyText & "', shall I capitalize the three letter word '" & Mid$(AnyText, intCounter + 1, 3) & "' to '" & UCase(Mid$(AnyText, intCounter + 1, 3)) & "' ?", vbYesNo, "Three letter word was found") = vbYes Then
                    'Yes was selected, so Capitalize the 3 char's
            '        AnyText = Left$(AnyText, intCounter) & UCase(Mid$(AnyText, intCounter + 1, 3)) & Mid$(AnyText, intCounter + 4, 255)
            '        intCounter = intCounter + 3
            '    Else
            '        'No was selected, so only capitalize the first of the 3 char's
            '        AnyText = Left$(AnyText, intCounter) & UCase(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText, intCounter + 2, 255)
            '    End If
            'End If
    End Select
Next
'All done, return current contents of AnyText variable.
fProperCase = AnyText
End Function

DCrake · May 22, 2008

Simple Software Solutions

The code looks fine, however it has to be maintained manually in code so if an unusual name say Van de Vaulk III appears you cannot trap it unless it appears in the select case element.

CodeMaster:

gemma-the-husky · May 22, 2008

is Fitz a problem as well

FitzWilliam etc (or is this not capitalised internally)

KenHigg · May 22, 2008

Funny we're all having this dialog while the original poster hasn't even checked back in

georgedwilkinson · May 22, 2008

But it's a fascinating topic with real-world implications.

I don't really care what the output looks like as long as I can get equality between similar names on different lists. For instance, is Ken McDonald in Jeejap the same as Kenneth MacDonald in JeGap?

Keeping a list of common aliases/mispellings/formats of names is an alternative I've been thinking about and this thread fits in with that concept.

But in the spirit of the question, it is much easier to convert to upper than it is to convert back to mixed case. A software or table based lookup is the way to go.

KenHigg · May 22, 2008

A hand full can be coded for but many will have to come from a conversion table

craigachan · May 22, 2008

Funny KenHigg. No I"m watching and learning from all of your experiance and am waiting for the magic answer.

DCrake · May 23, 2008

Simple Software Solutions

I know Willian FitzPatrick but does Partick FiztWilliam

gemma-the-husky · May 23, 2008

This is the sort of thing that doesn't matter to Access, as its case insensitive.

Its up to users really to key in properly in the first place. The programmer can automatically fix some issues, but not necessarily all. I would have thought that was the best you could get. (ie fixing obvious stuff)

What about eliminating spaces in double barrelled names, if users enter them wrongly.
is it Mary Pierce-Jones, or Mary Pierce - Jones

and if you do arbitrarily decide to eliminate all spaces, then there's a Dutch footballer playing in Scotland called

(surname) Vennegor of Hesselink

So perhaps its eliminate spaces ONLY around a hyphen ....

DCrake · May 23, 2008

Simple Software Solutions

For anyone who has used "Act" What it attempts to do is ask your for the surname and forename in two seperate fields however if you concat them with a space and it reads the name as the full name. However, if it counts more than one space it tries to be clever and comes up with a list box offering you all permutations. For example

Forename : Albert
Surname

icton Smith

Offers you

Forename:Albert
Surname

icton
or

icton Smith
or :Smith

If you select Picton Smith it strips this of the full name and assumes the first word is the forename.

Haven't used this for a long time, have never tried to emulate it neither.

Is it worth the effort?

CodeMaster:

craigachan · May 24, 2008

Thanks for all the posts. It look like I have my work set out for me.

Dealing with Names Mc* and Mac* (1 Viewer)

craigachan

Registered User.

DCrake

Remembered

KenHigg

Registered User

John.Woody

Registered User.

DCrake

Remembered

gemma-the-husky

Super Moderator

KenHigg

Registered User

georgedwilkinson

AWF VIP

KenHigg

Registered User

craigachan

Registered User.

DCrake

Remembered

gemma-the-husky

Super Moderator

DCrake

Remembered

craigachan

Registered User.

Similar threads

Users who are viewing this thread