Splitting names

[JHL]

Hello,
Using Access 97, I do not know VBA, but use the query design boxes fairly
well. I have a database where the Name field has the last and first names
separated by a comma.

The names are of varying lenght so using LEFT or RIGHT functions would not
give me a good result. Will someone show me how to split this one field into
two separate fields, Lastname - Firstname? Examples are:

CARSON,MYRNA
TREMBLAY,MARIE J
LYLE,JOHN
NESLUSAN,JOSEPH

etc..

Thank you in advance.
JHL

 

[John Spencer]

Field: LastName: Trim(Left(TheField,Instr(1,TheField & ",",",")-1))

Field: FirstName: Trim(Mid(TheField, Instr(1, TheField & ",",",")+1))

 

[Jeff L]

Use the InStr Function to find where your comma is. The syntax looks
like this:
InStr(string1, string2) where string1 is the string being searched (ie
your name field), and string2 is the string you are looking for (ie
","). The result is an integer that gives you the starting position of
string2. So for example InStr("Lyle, John", ",") would be 5, since the
comma is the 5th character in the string. Combine that with the Left
and Mid functions to get your first and last name.
LastName = Left([NameField],Instr([NameField], ",") - 1). Subtract 1
because you don't want the comma as part of your last name.
FirstName = Mid([NameField], Instr([NameField], ",") + 1). Add 1
because you want your first name to start with the character after the
comma.

Hope that helps you!

 

[JHL]

Thanks John & Jeff for your replies. I'm still having a problem.
I added fields Lastn and Firstn to my table. The combined name field is
Field2. Using an 'update' query' from the menu, my
Fieldname is Lastn; my Table is 'Names w Commas; and in the Update to box I
have: Lastn: Trim(Left([Field2],Instr(1,[Field2]& ",",",")-1))

but I get an error of Invalid Expression. .(dot) or ! operator or invalid
parentheses.

I get this error even if I remove the [] from the field name Field2. I also
get the same error with Jeff's example of writing the formula. Please help
again.

Thanks,
JHL

 

[Jeff L]

Take out the Lastn: in your Update To box and it should work.

 

[JHL]

I knew that!


NO I DIDN'T

It worked like a charm! THANKS SO MUCH!