StrConv and vbProperCase

[Question Boy]

Good morning,

I having a little issue and I'm hoping someone can give me a simple solution.

I need to control the formatting of input data as it is being taken from
another source which is uncontrolled and I can't make changes to. As such, I
wanted to use the StrConv & vbProperCase to ensure input such a individual
names, city name, etc where properly formatted.

In a general sense this works as planned/hoped for. However, on compound
names it does not. Is there a work around or another function that I am not
aware of?

Example:
StrConv("Jean-François",vbProperCase)
Returns: Jean-françois
and I want: Jean-François

Thank you!

QB

 

[Jeff Boyce]

So, how would YOU decide whether the correct form was

John von Neumann
or
John Von Neumann

Now, how will you tell Access to do it that way.

The ProperCase setting simply capitalizes the first character of each word
(at the start of a text string and after each space).

You'll need to either come up with a procedure you've found/developed or
rely on USB (using someone's brain).

Parsing names is much harder than it looks!

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Question Boy]

Thank you, I knew it might pose a prob. but had hoped!

I'll stop looking and start developing!

 

[John Spencer]

Here is sometning that may come close (or at leat give you a starting
point).

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
..