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

LVL 7
Plano TechIT SpecialistAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
test this codes

    'First convert to initial cap, followed by all lowercase.
    AnyText = UCase(Left(AnyText, 1)) & LCase(mid(AnyText, 2))

'ADD this lines    
    If InStr(AnyText, ",") = InStrRev(AnyText, ",") Then
        AnyText = Left(AnyText, InStr(AnyText, ",")) & UCase(mid(AnyText, InStr(AnyText, ",") + 1))
    End If

'end of codes to add

    'Look at each character, starting at the second character.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
baffy2000Commented:
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"
0
Plano TechIT SpecialistAuthor Commented:
Absolutely brilliant!
I had to create a seperate function because all of the fields that called this code would CAP everything after a comma.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Rey Obrero (Capricorn1)Commented:
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
0
Plano TechIT SpecialistAuthor Commented:
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
0
Plano TechIT SpecialistAuthor Commented:
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?
0
Rey Obrero (Capricorn1)Commented:
what exactly do you mean by { If I try to put a space } ? just typing a space and no more?
0
Plano TechIT SpecialistAuthor Commented:
Yes, I am trying to idiot proof it. I do not need my users seeing error messages if they don't need to.
0
Rey Obrero (Capricorn1)Commented:
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
0
Plano TechIT SpecialistAuthor Commented:
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
0
Rey Obrero (Capricorn1)Commented:
so the problem is not in the function code..
this is  a different problem
0
Plano TechIT SpecialistAuthor Commented:
No It is the same function, but I will start a new question anyways.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.