Concatenate Last Name First. 1

[lars7]

Hi,
I found this code and i want it to change: "Smith, John W" into: "John" but I can't find the way to get rid of the "W" I'm getting: "John W" can anyone help me with this.

LastNameF: IIf(InStr([Employee_Name],Chr$(44))>0,Mid([Employee_Name],InStr([Employee_Name],Chr$(44))+1),[Employee_Name])

 

[traingamer]

You can nest that inside another iif.

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[PHV]

You may try this:
Split(Trim(IIf(InStr(Employee_Name,","),Split(Employee_Name,",")(1),Employee_Name)))(0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi PHV,

The curser is stopping over the first "(" of "(1)" so it wont let me view.

 

[PHV]

OOps, arrays aren't permitted in JetSQL :~/
In a standard code moule create the following function:

Public Function getElem(str, delim As String, N As Integer)
If IsNull(str) Then Exit Function
Dim myArr
myArr = Split(str, delim)
If N >= 1 And N <= (1 + UBound(myArr)) Then
  getElem = Trim(myArr(N - 1))
End If
End Function

Now the formula:
getElem(Trim(IIf(InStr(Employee_Name,","),getElem(Employee_Name,",",2),Employee_Name))," ",1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Hi PHV,
Thanks for this it works great. I have another problem though, some of the names are upper case and some are proper case I had hoped to add something like: "StrConv([LastNameFirst],3" into the code before it goes into a report so that they are all proper case, will that still be possible?

 

[PHV]

StrConv(getElem(Trim(IIf(InStr(Employee_Name,","),getElem(Employee_Name,",",2),Employee_Name))," ",1), 3)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lars7]

Thanks PHV,
Exactly what I was looking for.