Text Parsing Issue 1

[Trogg]

Hello all,

I have a field of names that may or may not contain middle names and/or initials. Field content example is as follows:

Ramsey,Jonathan David
Smith,Elizabeth

No space before or after the comma... always a space between first and middle name/initial if there is one.I am trying to extract the first name to a field by itself. The following works as long as the name contains a space in it.

FName:Trim(Mid([NameString],InStr([[NameString],",")+1,InStr([NameString]," ")-(InStr(([NameString]),",")+1)))

It fails on the names that have no space or middle name/initial and prints #Error in the field. I am assuming some IIF statement is in order here. How can I take care of both situations and is there an easier or better way than what I have so far? Thanks in advance for any help!

 

[PHV]

You may try this:
Trim(Mid(NameString,InStr(NameString,",")+1,InStr(NameString [!]& " "[/!]," ")-(InStr((NameString),",")+1)))

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

 

[GhostWolf]

My personal favorite: Split the name field into an array, using the comma.

aryNames = Split(NameField,",")
LName = aryNames(0)
FName = aryNames(1)

If FName actually contains a middle name too, you could split it again, (on the space), or InStr, or...

 

[GhostWolf]

Oops!! Sorry, I just noticed that this is in an SQL thread. Guess that means I've got to defer to the mid/instr solutions.

 

[Trogg]

Thanks PHV! Works perfectly! ...star for you buddy!