This is intended for use with name, address, city fields that come in as all caps. It attempts to recognize punctuation & spaces, and capitalizes based on that. Feel free to tweak it for your own particular situation.

Function ProperCase(text)
    Dim s As String, t As String, x As Long
    If Not IsNull(text) Then
        If Len(text) > 0 Then
            s = LCase(text)
            For x = 1 To Len(s)
                t = Mid(s, x, 1)
                Select Case t
                    Case " ", "#", "-", "/", "("
                        ' next char is upper
                        If x < Len(s) Then
                            Mid(s, x + 1, 1) = UCase(Mid(s, x + 1, 1))
                        End If
                    Case "."
                        ' prev char is lower
                        If x > 2 And x < Len(s) Then
                            If Mid(s, x - 2, 1) = " " Or Mid(s, x - 2, 1) =
"." Then
                                Mid(s, x - 1, 1) = UCase(Mid(s, x - 1, 1))
                            End If
                        End If
                    Case "&"
                        If x < Len(s) Then
                            Mid(s, x + 1, 1) = UCase(Mid(s, x + 1, 1))
                        End If
                        If x > 1 Then
                            Mid(s, x - 1, 1) = UCase(Mid(s, x - 1, 1))
                        End If
                    Case Else
                End Select
            Next x
            Mid(s, 1, 1) = UCase(Mid(s, 1, 1))
        End If
    End If
    ProperCase = s
End Function