query to trim name field?
in [Queries]
|
From: Cam on 5 Mar 2010 09:59 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)
From: ghetto_banjo on 5 Mar 2010 10:08 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.
From: Jerry Whittle on 5 Mar 2010 10:26 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" wrote: > 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)
From: Cam on 5 Mar 2010 11:50 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" wrote: > 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" wrote: > > > 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)
From: Jerry Whittle on 5 Mar 2010 12:01
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" wrote: > 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" wrote: > > > 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" wrote: > > > > > 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) |