asked on

ProperCase Function in Access VB

I need to add 1 more line of code that will capitalize State abbreviations after a comma in my Location text box on the After Update Event).
The City and State (Location) are in 1 textbox and that will not change.
I am calling this ProperCase function from several events.

Thanks In Advance,
Mark

Public Function ProperCase(AnyText As String) As String
    '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 and such
                If Mid(AnyText, intCounter - 1, 1) = "M" Then
                    AnyText = Left(AnyText, intCounter) & UCase(Mid(AnyText, intCounter + 1, 1)) & Mid(AnyText, intCounter + 2, 255)
                End If
                
            Case "tx"
                If Mid(AnyText, intCounter - 1, 1) = "Tx" Then
                    AnyText = Left(AnyText, intCounter) & UCase(Mid(AnyText, intCounter + 1, 1)) & Mid(AnyText, intCounter + 2, 255)
                End If
    
            Case " "
                Select Case Mid(AnyText, intCounter + 1, 2)
                    Case "de"
                        'Add any other exceptions here Example: Oscar de La Hoya
                        If Mid(AnyText, intCounter + 3, 1) = " " Then
                         
                            AnyText = Left(AnyText, intCounter) & LCase(Mid(AnyText, intCounter + 1, 1)) & Mid(AnyText, intCounter + 2, 255)
                        Else
                            AnyText = Left(AnyText, intCounter) & UCase(Mid(AnyText, intCounter + 1, 1)) & Mid(AnyText, intCounter + 2, 255)
  
                        End If
                        
                    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

Open in new window

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

baffy2000

Is there going to be a space between the comma and the state abbreviation? If you don't know, then you're going to have to cover a lot. However, I would try to achieve it using Replace, and a string array of State abbreviations.

Assuming that there will be a space between the comma and the state abbreviation, define an array with elements like:

", AK"
", AL"
", AR"
", AZ"
... and so on.

Then do a loop like:

Dim i as Long
For i = 0 to UBound(StateArray)
AnyText = Replace(AnyText, StateArray(i), StateArray(i),,,vbTextCompare)
Next i

This will search, case-independent, for any of your states, and replace them with the UpperCase versions. If you need to account for there being no space after the comma, you will need to define another array, with the same elements as above but no space. If you need to account for multiple spaces, then I think you need to do it another way, although part of your 'text-cleaning' could be to remove double spaces before you start. e.g.

Do While Instr(AnyText," ") <> 0
AnyText = Replace(AnyText, " ", " ")
Loop

By the way, looking at your code, you know that Case "tx" will never run, don't you? Because it's got two characters.

Finally, although this won't help you here, it's worth noting that VB does have a ProperCase function in the way of StrConv. i.e.

StrConv("The grapes of wrath",vbProperCase) will return "The Grapes Of Wrath"

Plano Tech

ASKER

Absolutely brilliant!
I had to create a seperate function because all of the fields that called this code would CAP everything after a comma.

Rey Obrero (Capricorn1)

you can do a further test to see if there are only two characters at the end of the string with

If InStr(AnyText, ",") = InStrRev(AnyText, ",") Then
if len(trim(mid(AnyText, InStr(AnyText, ",") + 1))) =2 then
AnyText = Left(AnyText, InStr(AnyText, ",")) & UCase(mid(AnyText, InStr(AnyText, ",") + 1))
End If
end if

Plano Tech

ASKER

Capricorn1,
The second line of code you gave me works! Now no need to create a seperate function for the 1 field. Thanks again!

-Mark

Plano Tech

ASKER

Capricorn1,
One last question regarding this code:
If I try to put a space in one of the fields that use this code I get:

Run-time error '94':
Invalid use of Null

What piece of code am I missing to supress this error?

Rey Obrero (Capricorn1)

what exactly do you mean by { If I try to put a space } ? just typing a space and no more?

Plano Tech

ASKER

Yes, I am trying to idiot proof it. I do not need my users seeing error messages if they don't need to.

Rey Obrero (Capricorn1)

change the first line

If IsNull(Nz(AnyText, Null)) Then GoTo Exit_ProperCase

to

If IsNull(Nz(AnyText, Null)) or len(trim(anytext) & "")=0 Then GoTo Exit_ProperCase

Plano Tech

ASKER

I am still getting the same error.
When I choose DEBUG, it takes me to the call line:

Private Sub Job_Site_Contact_AfterUpdate()
Me![Job Site Contact] = ProperCase(Me![Job Site Contact])
End Sub

Rey Obrero (Capricorn1)

so the problem is not in the function code..
this is a different problem

Plano Tech

ASKER

No It is the same function, but I will start a new question anyways.