Help with ProperCase

[andy_dyer]

Hi

I'm using this code that I found on the internet to correct capitilisation

Public Function ProperCase(AnyText As Variant) As String
'r_cubed
    
    'Convert passed text to all lowercase. Use ProperCase() as you would a built-in Access function.
    'If passed value is a null, ignore all the stuff below.
    
    If IsNull(Nz(AnyText, Null)) Then GoTo Exit_ProperCase
        
    Dim intCounter As Integer, OneChar As String
    
    'First convert to initial cap, followed by all lowercase.
    AnyText = UCase$(Left$(AnyText, 1)) & LCase$(Mid$(AnyText, 2))
    
    'Look at each character, starting at the second character.
    For intCounter = 2 To Len(AnyText)
        OneChar = Mid$(AnyText, intCounter, 1)
        'If current character (OneChar) is a space or hyphen...
        Select Case OneChar
            Case "-", "/", ".", "'", "&", "(", " "
                '...convert the character after space/hyphen/slash/period/apostrophe/ampersand to uppercase.
                ' Such as A.B.C. Industries, Sharron O'Conner, B&B Mfg
                AnyText = Left$(AnyText, intCounter) & UCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText, intCounter + 2, 255)
            
            Case "c"
                ' Take care of the McAfee's, McDonalds, McLaughlins
                
                If Mid$(AnyText, intCounter - 1, 1) = "M" Then
                    AnyText = Left$(AnyText, intCounter) & UCase$(Mid$(AnyText, intCounter + 1, 1)) & Mid$(AnyText, intCounter + 2, 255)
                Else
                    ' Handle the MacDonalds, MacPherson and such
                    If (intCounter > 2 _
                      And Mid$(AnyText, intCounter - 2, 2) = "Ma") Then
                        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
        End Select
    Next
    
    'All done, return current contents of AnyText variable.
    ProperCase = AnyText
    
Exit_ProperCase:
    
End Function

My problem is that i have two areas this doesn't help

1) For degree titles i.e. PhD and MSc

2) For my company we have a set of "initials" to refer to eachother as these can range from two to five characters which should all be capitalised but without "." between the letters - if I need to I am happy to put anything round these characters to distinguish them i.e. [ ]

So for example we could have the sitiation where I want a project title to be

PhD Project - [ABCDE] (Blah Blah Blah Blah)

And with the code above I get

Phd Project - Abcde (Blah Blah Blah Blah)

Can anyone who can actually make any sense of the code above suggest some new "Cases" and their associated code that I can use?

Many thanks

 

[jdraw]

I suggest you use the function you found for the majority of your needs.
Create a function or other mean to handle the degrees -even a list of all you can think of, and make it a "standard lookup table" for all to share. No need to be doing this over and over, if you have an authoritative list.
As for your #2 requirement ????

Changing capitalization on names is not trivial. there are many functions/approaches. And not every situation works - at least in my experience.

Try the function you found, test it with many different combinations to see what works and what doesn't. Perhaps you can improve it to do more of what you need.

 

[mikengstrom]

Hi there,

I found this thread whilst looking for a casing function for access.
I'm using the above code but have encountered a problem with names that have c as the second character and begin with any character other than 'M'

eg. mcdonald is okay but scofield give the and error

I'm getting

Run-time error '5':
Invalid procedure call or argument

but if I remove all the Sc etc names the function runs fine.

It's this line of code that it stops on.

If (intCounter > 2 And Mid$(AnyText, intCounter - 2, 2) = "Ma") Then

I'm guessing you can't mid from 0 as intCounter = 2 and the above is trying to -2 from intCounter.

If I change the above code to

If (intCounter > 2 And Left$(AnyText, 3) = "Mac") Then

It works but than it won't do hyphenated Mac's eg Smythe-MacDonald.

granted its not going to be a common name.

Any Ideas?
cheers
mike