Splitting a Field Question

[KS31]

I have a query where I am pulling the customer name where it is formatted as
Last, First in most cases. However, there are customers listed where a
comma is not present and I don't want those split. I'm using the Left and
Mid function and I need the two new fields not to show any comma.

Example:
Current Name Last Name First Name
Smith, John Smith John
ABC Company ABC Company

If I use the following on Last I get an error on the ABC Company line.
Last: Left([Name],InStr([Name],", ")-1)
Is there a way that I can do this?

 

[KARL DEWEY]

Try this --
Last: IIF(InStr([Name],", ")>0, Left([Name],InStr([Name],", ")-1),
Left([Name],InStr([Name]," ")-1))

 

[John Spencer]

You could try the following two expressions. Note that I append a comma to
the end of the current name in the Instr function

LastName: Left([Current Name] , Instr(1,[Current Name] & ",",",")-1)

FirstName: Trim(Mid([CurrentName],Instr(1,[Current Name] & ",",",")+1))

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Try this --
Last: IIF(InStr([Name],", ")>0, Left([Name],InStr([Name],", ")-1),
Left([Name],InStr([Name]," ")-1))

--
KARL DEWEY
Build a little - Test a little

I have a query where I am pulling the customer name where it is formatted
as
Last, First in most cases. However, there are customers listed where a
comma is not present and I don't want those split. I'm using the Left
and
Mid function and I need the two new fields not to show any comma.

Example:
Current Name Last Name First Name
Smith, John Smith John
ABC Company ABC Company

If I use the following on Last I get an error on the ABC Company line.
Last: Left([Name],InStr([Name],", ")-1)
Is there a way that I can do this?