Help with Hyphenated Name

[TotallyConfused]

I have a columne (LastName) where there are hypehnated names. When I use the
strconv function, only the first part of the name gets capitalized. How do I
write a query or sql to initial capitalizede both parts of the name?
example: Smith-thomas vs Smith-Thomas. Thank you in advance for any help.

 

[Dale Fye]

Formatting names is almost as much of a pain as formatting addresses.
Here is a simple function that will capitalize the first character of each
part of a hyphenated last name.

Public Function HyphenatedName(strLastName As Variant) As Variant

Dim intCharPos As Integer

If IsNull(strLastName) Then
HyphenatedName = Null
Exit Function
End If

intCharPos = InStr(strLastName, "-")
Mid(strLastName, 1, 1) = UCase(Mid(strLastName, 1, 1))
If intCharPos = 0 Then
HyphenatedName = strLastName
Else
Mid(strLastName, intCharPos + 1, 1) = UCase(Mid(strLastName,
intCharPos + 1, 1))
HyphenatedName = strLastName
End If

End Function

HTH
Dale

 

[John Spencer]

Here is a function I wrote long ago that may help you. The function was
designed to force sentence case in paragraphs but it should

You would call it in query as follows (Note the space after the - in the
argument. That is needed to force capitals after the space as well as after
the dash.
You could also include other characters such as periods, underscores, etc.

SentenceCaseForTextBlock([FieldName],"- ")


Public Function SentenceCaseForTextBlock(strChange As String, _
Optional strCapsAfter As String = ".!?", _
Optional tfForceLowerCase As Boolean = True) As String
'===============================================================================
' Procedure : SentenceCaseForTextBlock
' Author : John Spencer
' Purpose : Applies sentence case to a text block
' Arguments : strchange is a string. Handles up to 32K characters.
' strCapsAfter determines when next letter should be capital
' tfForceLowerCase if true forces all letters to lowercase
except
' those capitalized by the strCapsAfter rule
'===============================================================================
Dim LoopCount As Integer '32K characters
'Dim LoopCount as Long (change to Long to handle more chars)
Dim tfChange As Boolean
Dim strAscii As Integer

If tfForceLowerCase = True Then
strChange = LCase(strChange)
End If

tfChange = True

For LoopCount = 1 To Len(strChange)

If tfChange = True Then

Mid(strChange, LoopCount, 1) = _
UCase(Mid(strChange, LoopCount, 1))

strAscii = Asc(Mid(strChange, LoopCount, 1))
tfChange = strAscii < 65 Or strAscii > 90

ElseIf InStr(1, strCapsAfter, _
Mid(strChange, _
LoopCount, 1), vbTextCompare) > 0 Then
tfChange = True
End If

Next LoopCount
SentenceCaseForTextBlock = strChange

End Function

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

 

[John Spencer]

Dale,
Pardon me, but I don't see how that will handle uppercase and lowercase
conversions for the rest of the string - unless you are suggesting the
poster passed the StrConv version to your function.

JOHN HOLDEN-SMYTHE
john holden-smythe
John Holden-Smythe

Should be all be returned as John Holden-Smythe if I understand the
poster's request. So I believe what you are saying is the user should call
your function this way

HyphenatedName(StrConv([SomeField],3))

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

 

[Dale Fye]

John,

Good point, I should have pointed out that I elected not to change any other
characters assuming that the names had completed any other changes that the
user chose to make. I didn't want to be responsible for changing McDonand to
Mcdonald or anythying like that.

Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

Dale,
Pardon me, but I don't see how that will handle uppercase and lowercase
conversions for the rest of the string - unless you are suggesting the
poster passed the StrConv version to your function.

JOHN HOLDEN-SMYTHE
john holden-smythe
John Holden-Smythe

Should be all be returned as John Holden-Smythe if I understand the
poster's request. So I believe what you are saying is the user should call
your function this way

HyphenatedName(StrConv([SomeField],3))

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

Formatting names is almost as much of a pain as formatting addresses.
Here is a simple function that will capitalize the first character of each
part of a hyphenated last name.

Public Function HyphenatedName(strLastName As Variant) As Variant

Dim intCharPos As Integer

If IsNull(strLastName) Then
HyphenatedName = Null
Exit Function
End If

intCharPos = InStr(strLastName, "-")
Mid(strLastName, 1, 1) = UCase(Mid(strLastName, 1, 1))
If intCharPos = 0 Then
HyphenatedName = strLastName
Else
Mid(strLastName, intCharPos + 1, 1) = UCase(Mid(strLastName,
intCharPos + 1, 1))
HyphenatedName = strLastName
End If

End Function

HTH
Dale
--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

 

[fredg]

Dale,
Pardon me, but I don't see how that will handle uppercase and lowercase
conversions for the rest of the string - unless you are suggesting the
poster passed the StrConv version to your function.

JOHN HOLDEN-SMYTHE
john holden-smythe
John Holden-Smythe

Should be all be returned as John Holden-Smythe if I understand the
poster's request. So I believe what you are saying is the user should call
your function this way

HyphenatedName(StrConv([SomeField],3))

Fritz von Clauswitz-van der Hof
where the von and the van der ought not be capitalized, as well as
names like McDaniels and Mcdaniels or O'Connor and O'connor where
either spelling is correct.

I resolved the issue for myself by using a table of exception names,
and offering the user the option to bypass changing the entered name.
This is especially useful when entering business names. ABC
Television, IBM Corporation, etc.