Search code examples
ms-access

Text before space in Access query

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!

Solution

  • 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.