Manipulate test string

[Gavx]

I have a field (FullName) which might contain either John Doe or John Doe(6). Also the case might be mixed.

The objective is to apply PROPER case to the first name, UPPER case to the second name and still display the value in parenthesis immediately after the second name.

I was going to use the INSTR function to identify the space as well as the ( and go using LEFT and MID from there...or is there a more eloquent way to do this?

thanks

 

[arnelgp]

use split function:

Public function fnProperAndUpper(s As String) As String
Dim var As Variant
var = Split(s, " ")
fnProperAndUpper = strconv(var(0), vbProperCase) & " " & ucase(var(1))
end function

 

[Gavx]

I know this is a minor thing but as I am a newbie at coding and trying to get it clear in my head...

Public function fnProperAndUpper(s As String) As String

would it be correct to declare s more clearly?
Say
Public function fnProperAndUpper(strNameSay As String) As String

Is that the way this can be done?

Also why is it declared a string twice?

 

[Grumm]

Yes you can rename that 's'. Just make sure you replace all the 's' in the code by that new name.

There are 2 declarations on that first line. One is to tell that the 's' must be a string. (the parameter of that function cannot contains numeric characters)
The second one is to tell what the function returns. In this case, the function returns a string with lower and upper cases.

 

[Gavx]

Do I just call this code with the statement

Me.txtMyField = fnProperAndUpper(s)

 

[jdraw]

Do I just call this code with the statement

Me.txtMyField = fnProperAndUpper(s)

No. The Me represents a control on a form or report, which you haven''t mentioned in your post.

The new Format Fullname = fnProperAndUpper(Fullname)