Parse Name with 2 different separators

[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?

 

[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))

 

[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 "* *";

 

[Qaspec]

you r the man...thanks

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/

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?

.