Splitting a Field Question


K

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

Advertisements

K

KARL DEWEY

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

Advertisements

J

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
..

KARL DEWEY said:
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


KS31 said:
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?
 

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