Capital Problem

[Awes]

Hi Everyone

In a database that I have been working on the user is required to enter names and addresses.

To overcome finger trouble by the user a short piece of code using the StrConv(string, conversion) command was inserted into the 'After Update' property as an event procedure to ensure that the first letter of each word in the entered string was always shown and stored in capitals.

This works well and the users were impressed at this autocorrection, as they are not the best typists in the world.

However they have recently highlighted a situation that I hadn't thought of and neither had they until it occurred the other day and they have asked if I could come up with something to autocorrect the problem.

The problem is with double barrelled or hyphentated names e.g. Harvey-Jones.

The current code will only capitalise the first letter e.g. Harvey-jones

Whilst code can be written to look at each character in a string in turn, until the end of the string is reached, and if the current character is a '-' convert the next character into uppercase I was wondering if there was a quick way of solving the problem.

Any thoughts or comments would be appreciated.

Cheers

Awes

 

[Mile-O]

I may have got my brackets wrong somewhere:

strText = IIf(Instr(1, strText, "-"), StrConv(Left(strText, InStr(1, strText, "-")), vbProperCase) & StrConv(Mid(strText, Instr(1, strText, "-")), vbProperCase), StrConv(strText, vbProperCase))


Also, this won't deal with other names like: McDonald, O'Donnell, d'Angelo, etc

There's a few suggestions to this solution in the Modules forum.

 

[Awes]

Hi Mile-O-Phile

Many thanks for the quick reply.

I'll give that a try.

I'll also have a look on the Modules forum.

You also picked up on my next thought which was handling other oddities with surnames, the Mc and Mac being the ones that sprang to mind.

If every eventuality was to be covered I'm sure it would be easier to send users on a typing course!!!

Cheers

Awes.

 

[ghudson]

You can thank r_cubed for this code for it handles special characters ["-", "/", ".", "'", "&"],
MacDonalds type names, spaces and more...

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

This in only one function from his awesome Capitalization routines (revisited - 2nd time round) sample.
You must be a member [it is free] of www.UtterAccess.com to download this sample.

HTH

 

[Awes]

Hi ghudson

Thanks for the info I will have to study it to understand what it is all doing but if it covers all eventualities that would be excellent.

Thanks also for the pointer to the other forum, I will have to pay it a visit.

Mile-O-Phile

Tried your suggestion and it almost worked first time round, however I needed to add '+1' in the 'Mid' section to move things to the first character to the right of the '-' so the complete line should read:

strText = IIf(Instr(1, strText, "-"), StrConv(Left(strText, InStr(1, strText, "-")), vbProperCase) & StrConv(Mid(strText, Instr(1, strText, "-")+1), vbProperCase), StrConv(strText, vbProperCase))

It now works a treat so many thanks for your help.

Cheers both

Awes.

 

[Mile-O]

No probs - I wrote it off the cuff so suspected I may have got something wrong.

 

[gold007eye]

Very helpful code, but how do I do this?

You can thank r_cubed for this code for it handles special characters ["-", "/", ".", "'", "&"],
MacDonalds type names, spaces and more...

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

This in only one function from his awesome Capitalization routines (revisited - 2nd time round) sample.
You must be a member [it is free] of www.UtterAccess.com to download this sample.

HTH

How would I go about adding more capitalization features to this code above? This works great for individuals, but the problem I am having is with Credentials, Company information, etc. not capitalizing. Also if you ever use the name Merrimack this code changes it to MerrimacK. What I am looking to do is add a list of things (or maybe it can look at a list from a table) and have it capitalize those specific items.

Examples:

LLC, PLLC, CRNA, MD, PC, PA, SC (and the list could continue as I come across new items.)

Any suggestions?

 

[Moniker]

There is a finite amount of business name capitalizations -- around 10-15 come to mind. Just place them in a table (t_Company_Caps or something) and use DLookups to find them. Things like LLC, LLP, etc. are your standard business name capitalizations. As you encounter new ones, add them to the table.

SJ's example will work for dashes. For Mc and Mac names, you can use a similar structure to the company capitalizations. Make a table with Mac, Mc, van, and whatever else you can think of. As new, non-standard names make themselves noticeable, add them to the table.

To handle things like Merrimack not becoming MerrimacK, only check the front of the string for the prefixes. (If Left(NameField,3)= "Mac", etc.)

 

[gold007eye]

Ok new problem I have run into with this code. If someone uses the name "Scott" or "Scot" it is throwing an error: "Run-time error '5': Invalid procedure call or argument" and these are the lines highlighted in the code. (From the code I listed in my previous post)

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

Any way of getting around this?

Edit: I have further narrowed it down to the "Sc" in "Scott" it seems to be hung up there, because if I do "S" or "Co" or "Cott" or "Cot" it works fine; if I do "Sc" it throws the error.

 

[Brianwarnock]

He has not allowed for other than Mc insert the following

ElseIf intCounter = 2 Then
AnyText = Left$(AnyText, intCounter) & Mid$(AnyText, intCounter + 1, 255)

before

Else
' Handle the MacDonalds, MacPherson and such


Brian

 

[neileg]

Some great ideas, here. Unfortunately, the world doesn't live by the rules. Macintosh and MacIntosh for example are both correct.

 

[Brianwarnock]

As a further comment the above is a quick and ugly fix that works and required little testing, but as Moniker noted the case c requires a rewrite as it is sloppily handled, it is of course easy to be critical after somebody has done the original work, I do not have the time today but anybody using the code should think about it.

Also IMHO one can try to be too clever and lock out genuine exceptions to the rules, there is no real substitute for careful typing and checking.
Is there a way to overide the AfterUpdate check?

Brian

oops Neil and i typing at the same time.

 

[gold007eye]

That is a good question. I was hoping in situations where the code doesn't work correctly it could thow an error message to the user and then have the ProperCase function ignored for that situation. Unfortunately I didn't write the original code and I couldn't quite understand how it was written as I am not that adept with VB functions. If anyone can show me a better way to handle the "c" code I would greatly appreciate it.

Basically I noticed that the way he wrote the code anything with the 2nd letter being a "C" cause the error. The only exception is of course "MC" I don't know if this helps.

 

[gold007eye]

He has not allowed for other than Mc insert the following

Thanks for that piece of code; it did the trick. Is it possible to add a line of code to that where I can say If anytext has "LLC" Or "MD" Or "PC",etc. (as a seperate word) then AutoCap those?
They would always be at the end of the provider name. I know Moniker said to use a table/dLookup, but going over the network I have found dLookup slows things down. For me it would be easier to just have the list in the ProperCase function that I could add to as needed.

Hypothetically, if I was to go with the dLookup method how would that work?