How would I get the text in a string before a space - and avoid an error if there is no space in the string?
For example, if I have a FirstName field with names that looks like this:
- John S
- Sally Q
- Thomas R
- Robert
- Mary J
I want the field in the query to return:
- John
- Sally
- Thomas
- Robert
- Mary
I tried the following field in my query but it is still returning the space and character after the space:
FName: Left([FirstName],IIf(Len(InStr([FirstName]," ")=0),Len([FirstName]),InStr([FirstName]," ")-1))
Thank you!
CodePudding user response：
This will work:
Left([First Name], IIf((InStrRev([First Name], " ", -1)-1)<= 0, Len([First Name]), InStrRev([First Name], " ", -1)-1))
CodePudding user response：
Consider:
Left(FirstName, IIf(InStr(FirstName, " ")=0, Len(FirstName), InStr(FirstName," ")-1))
Or
IIf(InStr(FirstName, " "), Left(FirstName, InStr(FirstName, " ")-1), FirstName)
This assumes all entries follow the example patterns provided, and not something like: Mary Jo R. Gets complicated if you want Mary Jo.