If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
query to trim name field?
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) |
#2
|
|||
|
|||
query to trim name field?
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. |
#3
|
|||
|
|||
query to trim name field?
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) |
#4
|
|||
|
|||
query to trim name field?
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) |
#5
|
|||
|
|||
query to trim name field?
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) |
#6
|
|||
|
|||
query to trim name field?
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 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) |
Thread Tools | |
Display Modes | |
|
|