Proper Name capitalizaion (custom function) barfs if apostrophe in wrong place

[peskywinnets]

So, someone put together a customer function for proper name capitalisation becuase the standard functions doesn't work for names containing apostrophes (e.g. a surname like o'neil will only get get capitalized to O'neil, whereas what we want is O'Neil), anyway it works a charm (it was created way back in 2005 - ...original post here https://bytes.com/topic/access/answers/191948-capitalising-names) ...but I've come across a situation that breaks it....if the apostrophe is at the end of the name.

here's the code....

Public Function ProperCaseName(NameIn As String) As String
NameIn = Replace(NameIn, " ", " ", Compare:=vbBinaryCompare)
NameIn = StrConv(NameIn, vbProperCase)
NameIn = FixName(NameIn, "'")
NameIn = FixName(NameIn, " mac")
NameIn = FixName(NameIn, " mc")
' Add other markers as appropriate
'Debug.Print NameIn
ProperCaseName = NameIn
End Function

Private Function FixName(ByVal NameIn, Marker)
Dim intInstr As Integer
intInstr = 1
intInstr = InStr(intInstr, NameIn, Marker, vbTextCompare)
Do While intInstr <> 0
intInstr = intInstr + Len(Marker)
If Len(intInstr) <= Len(NameIn) Then
[COLOR="Red"]Mid(NameIn, intInstr, 1) = UCase(Mid(NameIn, intInstr, 1))[/COLOR]
End If
intInstr = InStr(intInstr, NameIn, Marker, vbTextCompare)
Loop
FixName = NameIn
End Function

the name that broke it was rico pellicino' ....the function stops at the line I've highlighted in red - any top tips? (as it's beyond me at this stage of my Access learning trajectory!)

 

[sneuberg]

I think

If intInstr <= Len(NameIn) Then

would make more sense than

If Len(intInstr) <= Len(NameIn) Then

since the idea is to make sure the Start position of Mid doesn't exceed the length.

 

[peskywinnets]

I think

If intInstr <= Len(NameIn) Then

would make more sense than.

Hi Steve - yes, that sorted it...wow, thanks! (your input was timely, I was just rolling my sleeves up & no doubt would have made the situation even worse!)