if field has two spaces pull content to the left of second space

[johnmerlino]

Hey all,

I have this query:

SELECT p.aname INTO GenericForgottenWithoutInitial
FROM (SELECT Left([TheName],IIf(InStrRev([TheName]," ")>1, InStrRev([TheName]," ")-1,Len([TheName]))) AS aname FROM TheForgotten) AS p;

Problem is it pulls content to left of first space because of this part:

InStrRev([TheName]," ")-1,Len([TheName]))


I would like it to pull content to left of the second, not first space.

Thanks for response

 

[PaulO]

Try using the Trim() function around the expression, this will eliminate double-spacing leaving just the one space that you want to use.

 

[DCrake]

So are we talking about phrases/strings that can have one or more spaces or is there always going to be a set amount

Fred Albert Jones
Charles Philip Arthur George
Elizbeth Anne
Margaret

 

[Brianwarnock]

One would expect the Left function to pull data from the left of the control, I think it should be a Right function.

I believe that he wants to pull the last word.

Brian

 

[DCrake]

Try this

Public Function ParseWord(Anystring As String,Indicator As Boolean) As String
'/This function parses out either the left of the last space found or the right
'/of the last space found.
'/If no spaces are found the whole string is returned.

'/First test for a space in the string

If InStr(AnyString," ") = 0 Then
   ParseWord = AnyString
   Exit Function
End If

Dim P As Integer


'/If Indicator = True then assume Right
'/If Indicator = False then assume Left

If Indicator = True Then
   P = InStrRev(AnyString," ")
   ParseWord = Mid(AnyString,P+1) ' Everything after the last space found
Else
   P = InStr(AnyString," ")
   ParseWord = Left(AnyString,P-1) ' Everything upto the first space found
End If

End Function

 

[Brianwarnock]

A nice function David but OTT for what he wants I think

Just Mid([TheName],InStrRev([TheName]," ")+1) should do it

Brian

 

[DCrake]

It's called versitility Brian.

 

[vbaInet]

So yes some examples would help.

If we take this as an example, "Charles Philip Arthur George", do you want the outcome to be "Charles Philip Arthur"?

 

[Brianwarnock]

This

SELECT p.aname INTO GenericForgottenWithoutInitial

from post 1 leads me to believe that all he wants is the surname.

However this is confusing

I would like it to pull content to left of the second, not first space.

Which would mean Charles Philip from vba's example.

so some examples would be good.

David sometimes too much can confuse people who are just starting out.

Brian

 

[DCrake]

What happens if it is a double barrelled name

Antony Wedgewood Benn

Or

Davis Love III

 

[vbaInet]

My post was quite late. It was supposed to come before Brian's first post, but it would still be good to get some more info.