vbProperCase - multi hyphenated names

[tf37]

Need just a little bit of help here
I found the following code to help resolve hyphenated names, but need to tweak when you have more than one hyphen in a name
ie: the name is mary-joe jane > it works okay Mary-Joe Jane, but if the name is: mary-joe-jane smith it does Mary-Joe-jane Smith
I have a private sub and it calls the public function:

Private Sub PayTo_AfterUpdate()
' 09-16-20 tf attempt to have pay to name in proper case without manually doing it
' appears to work properly - yea!!
' does not handle hyphenated names
' found code to take care of hyphenated names and call a function

'Me.PayTo = StrConv(Me.PayTo, vbProperCase)
Me.PayTo = fProperCase(PayTo.Text)
End Sub


Public Function fProperCase(ByVal vName As String)
'received from jasonlewis and modified by redrumba on 10/26/2007
'Returns ProperCase, including hyphenated names and names with an apostrophe
'(i.e. bob->Bob; smith-jones; Smith-Jones and O'neill -> O'Neill)
Dim vReturn
Dim vLeft
Dim vRight
Dim lHyphen As Long
Dim lApostrophe As Long

vReturn = Null
lHyphen = Nz(InStr(1, vName, "-", vbBinaryCompare), 0)
lApostrophe = Nz(InStr(1, vName, "'", vbBinaryCompare), 0)

If Len(vName) Then

If lHyphen Then
vLeft = Mid(vName, 1, lHyphen - 1)
vRight = Mid(vName, lHyphen + 1)
vReturn = StrConv(vLeft, Conversion:=vbProperCase) & "-" & StrConv(vRight, Conversion:=vbProperCase)

Else
If lApostrophe Then
vLeft = Mid(vName, 1, lApostrophe - 1)
vRight = Mid(vName, lApostrophe + 1)
vReturn = StrConv(vLeft, Conversion:=vbProperCase) & "'" & StrConv(vRight, Conversion:=vbProperCase)
Else
vReturn = StrConv(vName, Conversion:=vbProperCase)
End If
End If

End If
fProperCase = vReturn
End Function

 

[rondeondo]

Hi TF
The built in function proper() works with hyphenated names for me, I'm using Excel 365.
If this is a new thing, an alternative is:

=SUBSTITUTE(PROPER(SUBSTITUTE(A1,"-","- ")),"- ","-")

This has the effect of putting a space in front of all the names following a hyphen then doing the proper case then removing the space again.

 

[tf37]

Thanks, I'll give something like that a try, but I'm using Access, not Excel : )
And it's an older version of Access from Office 2000.

 

[rondeondo]

oh sorry, I assumed excel. In Access there's a replace function which works pretty much the same as substitute in Excel and I think there's a proper in Access, but it may be called something else. Had a quick look, there's strconv with an option for proper case usage StrConv(<string variable>, vbProperCase)

 

[Joe4]

oh sorry, I assumed excel.

The question is posted in the "Microsoft Access" forum. If you came across this thread in the "Unanswered Threads" listing, note that the forum the thread is found in is right under the thread title in that listing.

 

[Micron]

vName = Replace(vName,"-","") ?

I might be missing the point because if you want to replace apostrophes as well, I'm not seeing a need to do stuff like Instr to find them and then use functions like Mid to remove them...

EDIT - thought about that some more. So I suppose that is being done in order to upper case O and N in O Neil?

 

[Micron]

Maybe it's as simple as

Function FixNames(vName As String) As String

FixNames = Replace(vName, "-", " ")
FixNames = Replace(FixNames, "'", " ")
FixNames = StrConv(FixNames, 3)
Debug.Print FixNames

End Function

OUTPUT:
fixnames("mary-joe-jane smith")
Mary Joe Jane Smith

fixnames("robert o'neil")
Robert O Neil

Depends on how it's being called/used or whether or not to remove the space in o'neil.
Your original problem might be caused by passing byVal or even using the Text property of the combo. Text is more difficult/dangerous than using its Value property (the default). Also, byVal creates a copy of the variable which then dies along with the procedure. If not handled correctly, you can lose it. Another potential issue is if you use the passed argument variable (vName) in the first line and set the function value to whatever you did to modify it, then use the variable thereafter, you're referring to the unmodified version of it. You'll notice that subsequently I used the function result instead. To make things a bit more intuitive and in the interest of reliability, I would create a variable and process that rather than use the function name to modify it further. At this point, I'm just playing around so I took an approach that I wanted to pare down as much as possible yet work according to how I interpret your post.

 

[JonXL]

OUTPUT:
fixnames("mary-joe-jane smith")
Mary Joe Jane Smith

fixnames("robert o'neil")
Robert O Neil

I think the OP wants to keep the hyphen, but your code gave me an idea...

Function FixName(strName As String) As String

FixName = Replace(strName, "-", Chr(0))
FixName = StrConv(FixName, vbProperCase)
FixName = Replace(FixName, Chr(0), "-")

End Function

?fixname("mary-joe-jane smith")
Mary-Joe-Jane Smith

 

[Micron]

Maybe we'll never know. Even after reviewing the OP and the notes within the code I can't make definitive sense of what the goals are. The post title is about hyphens but there's this mix of capitalization in there, and I couldn't tell what from what. I mean who knows at this point - what if the last pair was hyphenated? To my mind, the post doesn't have enough examples and desired outputs. I'd expect Mary-Joe to be hyphenated but not Joe-Jane for that matter. Anyway, I was just trying to show that based on what the sample code does, it appears that it can be condensed quite a bit.

 

[JonXL]

Couldn't get the apostrophe to play nice, but perhaps just replacing it with a different character is the way to do it....

Function FixName(strName As String) As String

    FixName = Replace(strName, "-", Chr(0))
    FixName = Replace(FixName, "'", Chr(11))
    FixName = StrConv(FixName, vbProperCase)
    FixName = Replace(FixName, Chr(0), "-")
    FixName = Replace(FixName, Chr(11), "'")

End Function

?fixname("mary-joe-jane smith o'niel")
Mary-Joe-Jane Smith O'Niel

All of this assumes you wouldn't have the Chr(0) (Null) or Chr(11) (vertical tab) in your name field - and I genuinely doubt that you would...