Changing the case in a sentance

[a.phillips]

Hi, in the database I am working in, there is a function, in a moduel, which puts the first letter in the first 2 words in uppercase (used for a name), e.g. john smith would become John Smith.

I want the code to work for more than 2 words (the number of words will change). How would I addapt the code to work for this?

Thanks

Code:

Public Function propercase(s As String) As String

propercase = UCase(Left$(s, 1)) & LCase(Mid$(s, 2, Len(s)))

If Mid$(s, 1, 2) = "Mc" Or Mid$(s, 1, 2) = "mc" Or Mid$(s, 1, 2) = "MC" Then
propercase = "Mc" & UCase(Mid$(s, 3, 1)) & LCase(Mid$(s, 4, Len(s)))
End If

If InStr(s, "'") Then
Dim sTemp As String
Dim strPos As Integer

strPos = InStr(propercase, "'") + 2

sTemp = UCase(Left$(propercase, 1)) & Mid$(propercase, 2, InStr(propercase, "'") - 1) & UCase(Mid$(propercase, InStr(propercase, "'") + 1, 1)) & LCase(Mid$(propercase, strPos, Len(propercase)))
propercase = sTemp
End If

If InStr(s, "-") Then

strPos = InStr(propercase, "-") + 2

sTemp = UCase(Left$(propercase, 1)) & Mid$(propercase, 2, InStr(propercase, "-") - 1) & UCase(Mid$(propercase, InStr(propercase, "-") + 1, 1)) & LCase(Mid$(propercase, strPos, Len(propercase)))
propercase = sTemp
End If

If InStr(s, " ") Then
strPos = InStr(propercase, " ") + 2
sTemp = UCase(Left$(propercase, 1)) & Mid$(propercase, 2, InStr(propercase, " ") - 1) & UCase(Mid$(propercase, InStr(propercase, " ") + 1, 1)) & LCase(Mid$(propercase, strPos, Len(propercase)))
propercase = sTemp

End If

End Function

 

[vbaInet]

It seems you're not aware of the StrConv() function. Have a look:

http://www.techonthenet.com/access/functions/string/strconv.php

 

[a.phillips]

That seems nice and easy - thanks for your help

 

[a.phillips]

So from this example:

StrConv ("TECH ON THE NET", 3) would return - "Tech On The Net"

Could i use
StrConv ("Field1", 3)

 

[vbaInet]

Yes you can. Give it a try.

 

[vbaInet]

Could i use
StrConv ("Field1", 3)

Actually, if that's a field, then you use:
StrConv ([Field1], 3