Change Case

[Ken Smith]

Create the following Module and name it ProperCase

Function Proper(strToChange As String) As String
' Converts string to Proper case
On Error Resume Next
Proper = StrConv(strToChange, vbProperCase)
End Function


The first letter of the first word and every other word
preceeded by a space will be capitalized when you run this
module against the contents of a field.

 

[John Spencer (MVP)]

Too bad that won't work on "p.o.". It will return "P.o.".

Paste the function below into a module and then call it. WATCH OUT for word
wrap breaking this in the wrong places.

Public Function Title_Case(strChange As Variant, _
Optional strAddedSeparators As String) As Variant
'AUTHOR: John Spencer
'LAST MODIFIED: June 30, 1999
'DESCRIPTION: Changes characters in string to
'Uppercase the first letter of each word
'EXAMPLE: Title_Case("a Little red engine/that could")
'returns "A Little Red Engine/That Could"

Dim intCount As Integer
Dim strSeparator As String
strSeparator = " -&({[/:." & Chr(34)
'Uppercase character after one of these separator characters
'Chr 34 is double quote mark

Title_Case = strChange
If VarType(strChange) = vbString Then

If IsNull(strChange) Then strChange = ""

If Len(strChange) > 0 Then
strSeparator = strSeparator & strAddedSeparators
strChange = UCase(Left(strChange, 1)) & _
LCase(Right(strChange, Len(strChange) - 1))
'Do the first letter

For intCount = 2 To Len(strChange)
If InStr(strSeparator, Mid(strChange, intCount - 1, 1)) <> 0 Then
'Letter follows a space, dash, &, etc.
strChange = Left(strChange, intCount - 1) & _
UCase(Mid(strChange, intCount, 1)) & _
Mid(strChange, (intCount + 1))
End If
Next intCount

Title_Case = strChange
End If

Else
Title_Case = strChange

End If 'vartype is string

End Function