Splitting a text column

[Guest]

Is there a query function that will split/cut a text field at the first blank space? For example, I have a complete name field: "DOE JOHN J." that I want to convert to last name only "DOE". Left() obviously doesn't work because the names in the column are different lengths.

 

[Cheryl Fischer]

Try this:

Left([MyNameField], InStr([MyNameField], " ")-1)

In the above expression, the InStr() function finds the location of the
first space and then backs up 1 byte.

hth,
--

Cheryl Fischer, MVP Microsoft Access
Law/Sys Associates, Houston, TX

Is there a query function that will split/cut a text field at the first

blank space? For example, I have a complete name field: "DOE JOHN J." that
I want to convert to last name only "DOE". Left() obviously doesn't work
because the names in the column are different lengths.

 

[Guest]

That's it! Thanks Cheryl.

 

[John Vinson]

I am attempting to accomplish the same task, actually would like to split the cell into first name last name though. Can this be accomplished through a query? Any help would be appreciated greatly.

You can use the various String functions. If you have names like

Barry White

you can use an expression like

FirstName: Left([Fullname], InStr([Fullname], " ") - 1)
LastName: Mid([Fullname], InStr([Fullname], " ") + 1)

or run an Update query updating (empty) name fields to these values.

Note that some names will require manual intervention or some
elaborate code:

Linda Lou Jones ' first name Linda Lou, just ask her
Towns van Zandt ' last name van Zandt, and a terrific musician
Kennedy ' that's his legal full name