Select field characters after 2nd comma

[Alfredomulet]

Hello everyone, how can I select all the characters from a field after the second comma (or space)?

I have:
FULL_NAME
ALFREDO JOSE PEREZ REVERTE

and I would like to select everything after the second space, that is: PEREZ REVERTE.

Cheers,
Alfredo

 

[traingamer]

One way:

 Mid([Names],[COLOR=red]InStr([COLOR=blue]InStr(1,[Names]," ")[/color]+1,[Names]," ")[/color])

But will only work if there are at least two spaces. The blue part gets the first space, the red part finds the second space and the mid function gets the rest of the string. You could use an iif() to validate.

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

 

[sfreeman]

Assuming the delimiter is always a space....

Use this query to select your data,

'***********************************************
SELECT
GetName([Name])

AS
[Values]

FROM
tblNames;

which uses the following function:

'***********************************************
Public Function GetName(strNameInput) As String
Dim strName() As String
strName() = Split(strNameInput)
GetName = strName(UBound(strName) - 1)
GetName = GetName & " " & strName(UBound(strName))
End Function



Sam_F
"90% of the problem is asking the right question.