Parse Name with 2 different separators

Q

Qaspec

I have a field with a full name in the following format

Smith, John A

I woul dlike to split out the Last and First name into their own field. I
can get the Last name with -

Left([CustomerName],InStr([CustomerName],", ")-1)

but I can't get the first name. The problem is that in some instances there
is a middle initial and in some records there isn't. Can I work around this
to just get the first name?
 
K

Ken Snell

Try this, assuming that there is always a space between first name and
middle initial:

Left(Mid([CustomerName], InStr([CustomerName], ", ") + 2),
IIf(InStr(Mid([CustomerName], InStr([CustomerName], ", ") + 2), " ") = 0,
Len(Mid([CustomerName], InStr([CustomerName], ", ") + 2)),
InStr(Mid([CustomerName], InStr([CustomerName], ", ") + 2), " ") - 1))
 
F

fredg

I have a field with a full name in the following format

Smith, John A

I woul dlike to split out the Last and First name into their own field. I
can get the Last name with -

Left([CustomerName],InStr([CustomerName],", ")-1)

but I can't get the first name. The problem is that in some instances there
is a middle initial and in some records there isn't. Can I work around this
to just get the first name?

Data in the field should be consistent.
If some records do, and some don't have a middle initial in the Name
field then most of your problem is allowing users to enter improper
data.
Why not include an Initial field in the table so users can enter the
initlal by itself?

Why do you think you are limited to using just one query?

Separate names into Last Name and Everything else:
Update YourTable Set YourTable.LastName =
Left([CustomerName],InStr([CustomerName],", ")-1),
YourTable.[FirstName] = Mid([CustomerName],InStr([CustomerName],",
")+2) Where [CustomerName] Like "*,*";

The above will split the names into the 2 fields, the Lastname and the
FirstName, with or without an initial.

Next run another Update Query to remove anything after a space, if
there is one, in the FirstName field:

Update YourTable.FirstName Set YourTable.FirstName =
Left([FirstName],InStr([FirstName]," ")-1) WHERE YourTable.[FirstName]
Like "* *";
 
Q

Qaspec

you r the man...thanks

Ken Snell said:
Try this, assuming that there is always a space between first name and
middle initial:

Left(Mid([CustomerName], InStr([CustomerName], ", ") + 2),
IIf(InStr(Mid([CustomerName], InStr([CustomerName], ", ") + 2), " ") = 0,
Len(Mid([CustomerName], InStr([CustomerName], ", ") + 2)),
InStr(Mid([CustomerName], InStr([CustomerName], ", ") + 2), " ") - 1))
--

Ken Snell
http://www.accessmvp.com/KDSnell/


Qaspec said:
I have a field with a full name in the following format

Smith, John A

I woul dlike to split out the Last and First name into their own field. I
can get the Last name with -

Left([CustomerName],InStr([CustomerName],", ")-1)

but I can't get the first name. The problem is that in some instances
there
is a middle initial and in some records there isn't. Can I work around
this
to just get the first name?


.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top