Parse name field

[eerich]

I've searched the thread and found some examples of parsing names in a query, however, i'm receiving an error (invalid call procedure) when trying to parse out first, middle and last names.
Data sample:
Roger R Rabbit
Glenn Brown



I've used the following:
SELECT
strconv(Geneva.UsrName,3) AS GenevaUsr,

strconv(Right([UsrName],Len([UsrName])-InStrRev([UsrName]," ")),3) AS LastName,

strconv(Left([UsrName],Instr([UsrName]," ")-1),3) AS Firstname

From Geneva;

I cant figure out how to get the middle name to a separate field. Is there an IIF statement that is missing? Any help is appreciated.

 

[Crowley16]

have you considered using the split function to split the name into an array, and then looping through the array?

lbound being the firstname, ubound being the last name and anything in between are middle names...

--------------------
Procrastinate Now!

 

[eerich]

I need to execute in a query. I'm not familiar with how to use the split function.

 

[Crowley16]

write a function which takes in a parameter and returns the various parts of a name (or 3 functions, one for each part of a name...)

then just call the function from your query.

use F1 to find out how to use split

--------------------
Procrastinate Now!