Unconcatenate Last Name, First Name Middle Initial

[surfbum3000]

I need to seperate the name field. I only need help with the middle name which is sometimes a name and sometimes an initial. [LASTNAME, FIRSTNAME MIDDLENAME] or [LASTNAME, FIRSTNAME MIDDLEINITIAL]

LastNAME: Left([SORT_NAME],InStr([SORT_NAME],", ")-1); Works fine

FirstNAME: Mid([SORT_NAME],InStr([SORT_NAME]," ")+1) ; Works fine.

MiddleName or MiddleInitial: ?? Would it be the Right function?

MiddleName: Right([SORT_NAME],InStr([SORT_NAME],",")+1); Does Not Work

 

[PHV]

MiddleName: Mid([SORT_NAME],InStrRev([SORT_NAME]," ")+1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[surfbum3000]

Sorry. Produced no results.

 

[PHV]

And this ?
MiddleName: Mid([SORT_NAME],InStrRev(RTrim([SORT_NAME]),' ')+1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gammyb]

I also need to unconcatenate a name field, and last name per surfbum3000's example works great, but I can't get the first name to work.

The format of my name field is: Lastname,Firstname
no space after the comma. So, for last name, this string works:
LastName: Left([name],InStr([name],",")-1)

But for first name, when I use the following string
Firstname: Mid([name],InStr([name]," "))+1
the query runs but returns #Error on all names.

I've tried every combination I can think of and I am stuck.

Thanks kindly!

 

[PHV]

Firstname: Mid([name],InStr([name],",")+1)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[cranebill]

Mid([name],InStr([name],","))+1

you said there is no space after the comma, so it is not finding one, instead look for the comma

 

[gammyb]

Thank you both, it works perfectly!

 

[Melagan]

To further understand how the left, right, mid, and instr functions work, I'd recommend going to immediate window (ctrl-g), type the name of the function then hit F1. The help files on these functions are great, and they plainly explain why Firstname: Mid([name],InStr([name]," "))+1 wouldn't work for a string that has names seperated by a comma and no space =)

~Melagan
______
"It's never too late to become what you might have been.