query to trim name field?

  • Thread starter Thread starter Cam
  • Start date Start date
C

Cam

Hello,

There is a name field (last, first) in my query that I would like to create
a new field that only return the last name. What is the proper function to
use? Thanks

Example:
Johnson, sam

Return:
Johnson (without comma)
 
You want to use a combination of the Instr() function and the Left()
function.

Instr() will find a specific string within a string, and will return
the starting position. So in this case, you want to find where that
comma is, and then return everything to the left of it.


Left([NameField], Instr([NameField], ",") -1 )


Since you do not want to include the comma itself, we subtact 1 one
from the result of the Instr function. Note this will return an
#Error if it doesn't find a comma, since Instr() returns 0 if string
not found, and you cant take the Left Negative 1 characters. So this
will work if EVERY record has a comma in it. Let me know if that is
not the case, and we could modify this a little.
 
LastName: Left([name])",Instr([name], ",")-1)

The above will work in a query. There must be a comma or it won't. Also make
sure that [name] is the correct field name.
 
Hello Jerry,

I put in your exact formula and I got this error popup.

"The expression you entered has a function containing the wrong number of
arguments"

Here is my query field:

Name: LEFT([tblOrder].[Planner])",Instr([tblOrder].[Planner], ",")-1)


Jerry Whittle said:
LastName: Left([name])",Instr([name], ",")-1)

The above will work in a query. There must be a comma or it won't. Also make
sure that [name] is the correct field name.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Cam said:
Hello,

There is a name field (last, first) in my query that I would like to create
a new field that only return the last name. What is the proper function to
use? Thanks

Example:
Johnson, sam

Return:
Johnson (without comma)
Click to expand...
Click to expand...
 
My Fault! I left in an extra ".

TheName: LEFT([tblOrder].[Planner]),Instr([tblOrder].[Planner], ",")-1)

BTW: I changed the first part to TheName:

Here's the reason: http://support.microsoft.com/kb/286335/
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Cam said:
Hello Jerry,

I put in your exact formula and I got this error popup.

"The expression you entered has a function containing the wrong number of
arguments"

Here is my query field:

Name: LEFT([tblOrder].[Planner])",Instr([tblOrder].[Planner], ",")-1)


Jerry Whittle said:
LastName: Left([name])",Instr([name], ",")-1)

The above will work in a query. There must be a comma or it won't. Also make
sure that [name] is the correct field name.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


Cam said:
Hello,

There is a name field (last, first) in my query that I would like to create
a new field that only return the last name. What is the proper function to
use? Thanks

Example:
Johnson, sam

Return:
Johnson (without comma)
Click to expand...
Click to expand...
Click to expand...
 
This will work whether of not there is a comma in the field or not

LEFT([NameField], INSTR(1,[NameField] & ",",",")-1)

It will work for nulls, zero-length strings, and fields that don't contain a
comma, The trick is ensuring that the Instr can always find a comma by adding
a comma to the end of the string.

Null returns "" (a zero length string)
"" returns ""
Spencer returns Spencer
Spencer, JP returns Spencer
de la Hoya, Oscar returns de la Hoya

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top