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
mmarisAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Rey Obrero (Capricorn1)Connect With a Mentor 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
 
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
 
mmarisAuthor 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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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
 
mmarisAuthor 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
 
mmarisAuthor 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
 
mmarisAuthor 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
 
mmarisAuthor 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
 
mmarisAuthor Commented:
No It is the same function, but I will start a new question anyways.
0
All Courses

From novice to tech pro — start learning today.