Proper case function. 4

[RMasters]

I want to alter a series of records to "proper case" i.e "rick masters" changes to "Rick Masters". Is there an access function for this?

Many thanks in advance.

 

[PaulF]

StrConv("TextToChange", vbProperCase)

PaulF

 

[JaneInMA]

I use this code, put in a module then call it where you want


Public Sub ConvertTableCase(strTable As String, strField As String)
Dim Db As Database
Dim Rs As RecordSet
Dim strSQL As String, strCase As String
strSQL = "SELECT " & strField & " FROM " & strTable & " WHERE " & " " & strField & " is NOT NULL"
Set Db = currentDB()
Set Db = currentDB()
Set Rs = Db.OpenRecordset(strSQL, dbOpenDynaset)

If Rs.RecordCount > 0 Then
With Rs
.MoveFirst
Do Until .EOF
strCase = ChangeCase(Rs(strField))
.Edit
Rs(strField) = strCase
.Update
.MoveNext
Loop
End With
Else
Exit Sub
End If

Rs.Close
Db.Close

'MsgBox "Process Complete"
End Sub

This sub can accept null fields without errors.

Call it by this piece of code

Call ConvertTableCase("TableName", "FieldName&quot

Obviously you put in your table name and field name where I have the words. They are strings and hence must be enclosed. Decide yourself if you want a message when you are finished. In the code above I commented it out.

 

[LarryDeLaruelle]

Paul:

StrConv works fine with 'normal' names but fails on hyphenated names or names that contain an "Mc".

I did this function which accounts for most of that. Not perfect but it has served me well so far:

'This function will take a name typed in in any case and change it to mixed case. If there is a
'space in the name the next letter will be capitalized; if there is a hyphen in the name, the next
'letter will be capitalized; if there is an apostrophe in the name, the next letter will be
'capitalized; if the name begins with Mc, the next letter will be capitalized.

Public Function NameParse(NameIn As String)
Dim intLength As Integer
Dim intloop As Integer
Dim strOutName As String

intLength = Len(NameIn)

strOutName = UCase(Left(NameIn, 1)) & LCase(Mid(NameIn, 2, 1))

For intloop = 3 To intLength
'Tests for hyphen, space or apostrophe in the previous letter position and
'capitalizes the current letter
If Mid(NameIn, intloop - 1, 1) = "-" _
Or Mid(NameIn, intloop - 1, 1) = " " _
Or Mid(NameIn, intloop - 1, 1) = "'" Then
strOutName = strOutName & UCase(Mid(NameIn, intloop, 1))

'Test for the combination Mc in the previous two letter positions and
'capitalizes the current letter
ElseIf Mid(NameIn, intloop - 2, 1) = "m" And Mid(NameIn, intloop - 1, 1) = "c" Then
strOutName = strOutName & UCase(Mid(NameIn, intloop, 1))

'If none of the above conditions evaluate to True, makes the current letter lower case
Else
strOutName = strOutName & LCase(Mid(NameIn, intloop, 1))
End If
Next intloop

NameParse = strOutName

End Function


Larry De Laruelle
larry1de@yahoo.com

 

[lebanoncedars]

Hi there,
Why don't you use this code ONLY and place it in any field you want to capitalize first letter of each word.
it works and easy.

Me![name of field] = StrConv([name of field], vbProperCase)

Thanks,
Lebanoncedars