String question

[Tcs]

I *think* I've seen what I want to ask, but I've tried it and can't get it to
work. So I figure that I'm remembering wrong or just have the wrong syntax. (My
*usual* problem.)

I have two separate fields of names, usually in the format of LAST, FIRST MI.
One column may or may not have commas. On column may or may not have the middle
initial. But both *usually* start with the last name.

If I want to grab the text UPTO the first space or comma, can't I do something
like:

LastName = left$([Column1]," ")

(I can't get it to work.) Or do I have to add code to check all the positions
starting at the beginning looking for my space or comma?

I appreciate the assist, thanks in advance,

Tom

 

[Guest]

Backup your database. Create a field named FIRST and another named MI.
Update FIRST with --
Left([FIRST MI], InStr([FIRST MI]," ")-1)

Update MI with --
Rightt([FIRST MI], Len([FIRST MI]) - InStr([FIRST MI]," "))

Verify the updates and then delete the field FIRST MI.

 

[John Spencer]

You are looking for the Instr function.

IIF (Instr(1,[Column1]," ")=0,Null,Trim(Left([Column1],Instr(1,[Column1],"
")))

That still leaves you with a trailing comma in some cases. You can wrap the
whole thing in the replace function and get rid of the comma.

Also note that I choose to return Null if there was no space in the name.
You could replace Null with [Column1] and get the entire contents into the
last name. ALSO, If the name is something like "Van Hooten Mike A", you are
going to end up with "Van" as the last name

TEST TEST TEST on a copy of your data.