Text format

[Guest]

Hello

I have a Table where info has been entered any way the user felt like. How can I easily change the format the a specific column info so that they have the first letter of each word capitalized only

Daniel

 

[fredg]

Hello,

I have a Table where info has been entered any way the user felt
like. How can I easily change the format the a specific column
info so that they have the first letter of each word capitalized
only?

Daniel

To permanently change the exiting table data, run an Update query:

Update YourTable Set YourTable.FieldName = StrConv([FieldName],3);

To correct data BEFORE it is saved in the table, use a form to enter
the data.
Code the Control's AfterUpdate event:

[ControlName]= StrConv([ControlName],3)

This Will Capitalize The First Letter Of Each Word.

Unfortunately, it will incorrectly capitalize some names which should
have more than one capital letter in them, i.e. McDonald, O'Hare, IBM,
CBS, etc.
You can use an exceptions table to take care of this, but then there
are people and places whose capitalization is a matter of personal
preference, O'connor & O'Connor, Mcdonald & McDonald, while some
names are not capitalized at all... van Beethoven, van den Steen, and
e. e. cummings.

Have fun.

 

[Bryan Martin]

This may not be the best way but will work.

Public Sub CapFirstLetter()
Dim obj_Conn As ADODB.Connection
Set obj_Conn = CodeProject.Connection

Dim obj_RS As ADODB.Recordset
Set obj_RS = New ADODB.Recordset

obj_RS.Open "SELECT ID, ClientName FROM BM_tblClientSystemNames",
obj_Conn, adOpenStatic, adLockOptimistic
Dim ary_SplitField() As String
Dim str_SplitField As String
Dim str_Char As String
Dim i As Integer

Do Until obj_RS.EOF
str_SplitField = obj_RS(1).Value
ary_SplitField = Split(str_SplitField, " ")
For i = 0 To UBound(ary_SplitField)
If Len(ary_SplitField(i)) > 0 Then
str_Char = UCase(Mid(ary_SplitField(i), 1, 1))
ary_SplitField(i) = str_Char & Mid(ary_SplitField(i), 2)
End If
Next
str_SplitField = Join(ary_SplitField, " ")
obj_RS(1) = str_SplitField
obj_RS.Update
obj_RS.MoveNext
Loop

If obj_RS.State <> 0 Then obj_RS.Close
Set obj_RS = Nothing
If obj_Conn.State <> 0 Then obj_Conn.Close
Set obj_Conn = Nothing
End Sub


Bryan Martin
(e-mail address removed)

Hello,

I have a Table where info has been entered any way the user felt like.

How can I easily change the format the a specific column info so that they
have the first letter of each word capitalized only?