Need to pull just first name in a column with L, F M

W

Wm

I have a table with a name column that contains last, first middle. I have
used Left$([Name],InStr(1,[Name],",")-1) to pull the last name and it works
good. I am having trouuble pulling the first name only. Any suggestions?

Wm
 
S

storrboy

I have a table with a name column that contains last, first middle. I have
used Left$([Name],InStr(1,[Name],",")-1) to pull the last name and it works
good. I am having trouuble pulling the first name only. Any suggestions?

Wm
Click to expand...


Are you doing this to just read the values in the field, or are you
trying to separate them into to different fields?
I personally would store first and last names in separate fields.

You may be able to use the same logic as your example, except use the
Mid function instead of Left.

Mid([Name],IIF(InStr(1,[Name],",")=0,len([name]),InStr(1,[Name],","))
+1) - assuming there is no space before the first name, I I used the
IIF to force the mid string to start at the end of the name if InStr
returned 0 for any reason.
 
S

storrboy

I have a table with a name column that contains last, first middle. I have
used Left$([Name],InStr(1,[Name],",")-1) to pull the last name and it works
good. I am having trouuble pulling the first name only. Any suggestions?

Wm
Click to expand...


In my example I forgot about the middle name, but maybe you can figure
it out from that.
 
W

Wm

Thanks

Well, that is pretty much my question - your expample does seperate the last
name from the First & Middle - but how do I just make it pull the FIRST name.
Again, I have the last - just need the first - not everything after the
comma.

Wm

storrboy said:
I have a table with a name column that contains last, first middle. I have
used Left$([Name],InStr(1,[Name],",")-1) to pull the last name and it works
good. I am having trouuble pulling the first name only. Any suggestions?

Wm
Click to expand...


In my example I forgot about the middle name, but maybe you can figure
it out from that.
Click to expand...
 
D

Douglas J. Steele

Unfortunately, it's not that simple. Some first names are compound (Mary
Lou, Billy Joe, etc.) It would be wrong to simply use Mary or Billy in those
cases.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Wm said:
Thanks

Well, that is pretty much my question - your expample does seperate the
last
name from the First & Middle - but how do I just make it pull the FIRST
name.
Again, I have the last - just need the first - not everything after the
comma.

Wm

storrboy said:
I have a table with a name column that contains last, first middle. I
have
used Left$([Name],InStr(1,[Name],",")-1) to pull the last name and it
works
good. I am having trouuble pulling the first name only. Any
suggestions?

Wm
Click to expand...


In my example I forgot about the middle name, but maybe you can figure
it out from that.
Click to expand...
Click to expand...
 
Top