Create a public module and copy the following function to it;
Public Function Proper(X)
' Capitalize first letter of every word in a field.
' Use in an event procedure in AfterUpdate of control;
' for example, [Last Name] = Proper([Last Name]).
' Names such as O'Brien and Wilson-Smythe are properly capitalized,
' but MacDonald is changed to Macdonald, and van Buren to Van Buren.
' Note: For this function to work correctly, you must specify
' Option Compare Database in the Declarations section of this module.
Dim Temp$, C$, OldC$, i As Integer
If IsNull(X) Then
Exit Function
Else
Temp$ = CStr(LCase(X))
' Initialize OldC$ to a single space because first
' letter needs to be capitalized but has no preceding letter.
OldC$ = " "
For i = 1 To Len(Temp$)
C$ = Mid$(Temp$, i, 1)
If C$ >= "a" And C$ <= "z" And _
(OldC$ < "a" Or OldC$ > "z") And _
InStr("1234567890", OldC$) = 0 Then
Mid$(Temp$, i, 1) = UCase$(C$)
End If
OldC$ = C$
Next i
Proper = Temp$
End If
End Function
Then call the function in the after update of a control like;
Private Sub Address_AfterUpdate()
Me.Address = Proper(Me.Address)
End Sub
You might want to add your own error handling.
BTW - I did not write this code (I'm not that smart) but I use it in my DB
and it works well.
HTH
Instructor Cloud said:
The above works for the first word in a field, but it doesn't work for a
field containing two or more words such as a street name with two words such
as "Stone Canyon".
Is there a way to force the data to show up as "Stone Canyon" instead of
"stone canyon"?
