Lawrence Salvucci
asked on
Proper Case in Access
I'm using the following code to capitalize company names, etc. But when a company name is like "ABC Co" it capitalizes the ABC just fine but when I put a comma after it like "ABC, Inc" it changes it to "Abc, Inc". It only does this when I put a comma after the name. Anyway to fix this so it changes it to ABC, Inc.?
I'm calling the code like this: fProperCase(MyField,1)
I'm calling the code like this: fProperCase(MyField,1)
Function fProperCase(Optional strText As String, Optional blPrompt As Boolean) As String
'Call this function in this manner:
' ProperCase("yourTexthere")
' If you would like to automatically capitalize all 2 or 3 character words, then call in this manner:
' ProperCase(yourTexthere,1)
' Please note any two or three character words in the string will automatically capitalize if all
' of the characters in the string are the same, regardless of the value of this parameter (AAA Insurance Company)
Dim intCounter As Integer
Dim OneChar As String
Dim StartingNumber As Integer
StartingNumber = 1
If Nz(strText, "") <> "" Then 'If value is not blank, then continue below
'******************** Start - Check for 3 character words at the start of string
If Right(Left(strText, 4), 1) = " " Then
If (Left(strText, 1) = Mid$(strText, 2, 1) And Left(strText, 1) = Mid$(strText, 3, 1)) Or blPrompt = True Then
'Capitalize the 3 char's as in "AAA" or "EEE Movers"
strText = UCase(Left$(strText, 3)) & LCase$(Mid$(strText, 4, 255))
StartingNumber = 4
Else
'Only capitalize the first of the 3 char's
'This part can be removed if you do not want the 1st letter capitalized
strText = UCase$(Left$(strText, 1)) & LCase$(Mid$(strText, 2, 255))
StartingNumber = 2
End If '******************** End - 3 letter check at beginning of string
ElseIf Right(Left(strText, 3), 1) = " " Then 'Check for 2 character words as the start of the string
If Left(strText, 1) = Mid$(strText, 2, 1) Or blPrompt = True Then
'Capitalize the 2 char's
strText = UCase(Left$(strText, 2)) & LCase$(Mid$(strText, 3, 255))
StartingNumber = 3
Else
'Only capitalize the first of the 2 char's
'This part can be removed if you do not want the 1st letter capitalized
strText = UCase(Left$(strText, 1)) & LCase$(Mid$(strText, 2, 255))
StartingNumber = 2
End If '***************** End 2 character word Check
Else
'The first word is not 2 or 3 char in length, so convert first character to capital then the rest to lowercase.
strText = UCase$(Left$(strText, 1)) & LCase$(Mid$(strText, 2, 255))
StartingNumber = 2
End If
'Look at each character, starting at the second character.
For intCounter = StartingNumber To Len(strText)
OneChar = Mid$(strText, intCounter, 1)
Select Case OneChar
'...convert the character after dash/hyphen/slash/period/ampersand to uppercase such as "A.B.C. Industries", B&B Mfg
Case "-", "/", ".", "&", "+"
strText = Left$(strText, intCounter) & UCase$(Mid$(strText, intCounter + 1, 1)) & Mid$(strText, intCounter + 2, 255)
Case "'" 'Check the character two places after the apostrophe.
If Mid$(strText, intCounter + 2, 1) <> " " Then 'If it is not a space, then capatilize (O'Conner, O'Niel)
strText = Left$(strText, intCounter) & UCase$(Mid$(strText, intCounter + 1, 1)) & Mid$(strText, intCounter + 2, 255)
Else
'Do nothing as in "Don't" , "Tom's Diner", "haven't", "I'm"
End If
Case "c" ' Take care of the McAfee's, McDonalds & McLaughlins and such
If (Mid$(strText, intCounter - 1, 2) = "Mc") Then 'Check if Prev Char is an M
If ((intCounter - 2) < 1) Then 'Check to see if the M was the first character
strText = "Mc" & UCase$(Mid$(strText, intCounter + 1, 1)) & Mid$(strText, intCounter + 2, 255)
ElseIf (Mid$(strText, intCounter - 2, 1) = " ") Then
'If M wasn't first character, then check to see if a space was before the M, so we don't capitalize Tomchek or Adamczyk
strText = Left$(strText, intCounter) & UCase$(Mid$(strText, intCounter + 1, 1)) & Mid$(strText, intCounter + 2, 255)
End If
End If
Case " "
Select Case Mid$(strText, intCounter + 1, 3)
Case "de " 'Add any other exceptions here Example: Oscar de La Hoya or maria de jesus
strText = Left$(strText, intCounter) & "de " & Mid$(strText, intCounter + 4, 255)
intCounter = intCounter + 2
Case Else ' Example: A B C Manufacturing
strText = Left$(strText, intCounter) & UCase$(Mid$(strText, intCounter + 1, 1)) & Mid$(strText, intCounter + 2, 255)
End Select
If Mid$(strText, intCounter + 1, 9) = "diMartini" Then 'Add any other odd balls in this fashion
strText = Left$(strText, intCounter) & "diMartini" & Mid$(strText, intCounter + 10, 255)
End If
'Method for adding new case is fairly simple, such in the example above: "de "
'If Mid$(strText, intCounter + 1, Len("YourExclusionHere")) = "YourExclusionHere" Then
' strText = Left$(strText, intCounter) & "YourExclusionHere" & Mid$(strText, intCounter + (LEN("YourExclusionHere")+1), 255)
'End If
'*********************** Check for 3 character word *******************
If Mid$(strText, intCounter + 4, 1) = " " Or (Len(strText) - intCounter = 3) Then
If (Mid$(strText, intCounter + 1, 1) = Mid$(strText, intCounter + 2, 1) And _
Mid$(strText, intCounter + 1, 1) = Mid$(strText, intCounter + 3, 1)) Or blPrompt = True Then
'Capitalize the 3 char's as in "AAA" or "EEE Movers"
strText = Left$(strText, intCounter) & UCase(Mid$(strText, intCounter + 1, 3)) & Mid$(strText, intCounter + 4, 255)
intCounter = intCounter + 3
Else
'Only capitalize the first of the 3 char's
'This part can be removed if you do not want the 1st letter capitalized
strText = Left$(strText, intCounter) & UCase$(Mid$(strText, intCounter + 1, 1)) & Mid$(strText, intCounter + 2, 255)
End If
'********************** check for 2 char words *******************
ElseIf Mid(strText, intCounter + 3, 1) = " " Or (Len(strText) - intCounter = 2) Then
If (Mid(strText, intCounter + 1, 1) = Mid(strText, intCounter + 2, 1)) Or blPrompt = True Then
'Capitalize the 2 char's
'This part can be omitted if you do not want to automatically capitalize a 2 character word made up of two identical letters
strText = Left$(strText, intCounter) & UCase(Mid$(strText, intCounter + 1, 2)) & LCase$(Mid$(strText, intCounter + 3, 255))
intCounter = intCounter + 2
Else
'Only capitalize the first of the 2 char's
strText = Left$(strText, intCounter) & UCase(Mid$(strText, intCounter + 1, 1)) & Mid$(strText, intCounter + 2, 255)
intCounter = intCounter + 1
End If
'******************** END 2 LETTER CHECK
End If
Case Else
End Select
Next
Else
strText = ""
End If
'All done, return current contents of strText variable.
fProperCase = strText
End Function
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Nevermind. I got it working. I had to remove the second "IF" in your code.
ASKER