Strconv function

[grippy]

Hi

Does anyone know how you can convert fields to proper case but manage to
replace the letter after a charcter also? e.g. SMITH-WESSON to Smith-Wesson.
The strconv just converts it to Smith-wesson.

Thanks

 

[fredg]

Hi

Does anyone know how you can convert fields to proper case but manage to
replace the letter after a charcter also? e.g. SMITH-WESSON to Smith-Wesson.
The strconv just converts it to Smith-wesson.

Thanks

One way is to have a table of exceptions (such as McDonald, O'Brien,
Smith-Wesson, etc.) and using code, search the table before using
strConv() to see if that name is in the table, and give the user the
ability to over-ride the StrConv().
Remember, not only do some names require more than one capital, but
some times, no capital at all, i.e. van der Steen,while some names are
capitalized according to that persons wishes. O'connor and O'Connor
are both correct.

 

[grippy]

Thanks but i it would be way too time consuming to create a new table for
over 1/2 million records. I know this can be done in other databases with a
decode function or vb proper - there has to be a simpler way.

Thanks again for taking the time to reply.

 

[fredg]

Thanks but i it would be way too time consuming to create a new table for
over 1/2 million records. I know this can be done in other databases with a
decode function or vb proper - there has to be a simpler way.

Thanks again for taking the time to reply.

Where did you arrive a table of over 1/2 million records.
All you need is a table of the known names with more than one capital
letter (McDonald, O'Brien) as well as names that might have no
capitals (van Beethoven). Probably no more than 50 to 100 names to
start. More can be added as they are encountered.

 

[John Spencer]

You could write your own custom function. This is one that I wrote long
ago. It may not be the most efficient and with half a million records it
will be slow

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 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)
'UCase any letter that follows a space, dash, &, etc.
If InStr(strSeparator, Mid(strChange, intCount - 1, 1)) <> 0 Then
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

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..