Re: Separating People
- From: "Brian" <btmink@xxxxxxxxxxxx>
- Date: Wed, 24 May 2006 14:45:48 -0700
Doh! And I thought I was done! Thats a good idea, I will change it that way.
Thanks, yet again,
Brian
"Rick B" <Anonymous> wrote in message
news:ekVRws2fGHA.4792@xxxxxxxxxxxxxxxxxxxxxxx
Note that I would probably use the "Left" statement below, but I would not
use the "right" statement. The reason is that it would lose data.
Maria de los Sotos would become Maria (First) and Sotos (Last).
You'd lose all the middle.
I'd use a "MID" statement to grab everything after the first space and
store that as my last name. It will still be wrong sometimes (Mary Alice
Smith) but at least it won't miss data.
--
Rick B
"Rick B" <Anonymous> wrote in message
news:eCDX6n2fGHA.1260@xxxxxxxxxxxxxxxxxxxxxxx
One more example "Carla de los Sotos"
By the way, here is a previous post that answers how to do this. It is
from "Sprinks" who posts some great responses in the groups!
--------------------------------------
If they are separated by a space, as in "John Smith", add two new fields
to
your table, e.g., FName and LName, and use an update query to parse the
name
into the two fields, using the Left, Right, and InStr functions. For an
initial field named "StudentName", the SQL is:
UPDATE Students SET Students.FName =
Left([StudentName],InStr([StudentName]," ")-1), Students.LName =
Right([StudentName],Len([StudentName])-InStr([StudentName]," "));
See VBA Help for more information on the string functions.
Hope that helps.
Sprinks
"Brian" <btmink@xxxxxxxxxxxx> wrote in message
news:mr2dg.2$gC.0@xxxxxxxxxxxxxxxxxxxxxxxxxxx
I have been given a new requirement for a database that I have been using
for a long time. I have a Name field that needs to show as FirstName and
LastName for one report. I am having a hard time figuring out how to
modify the query so that it only calls up the first or last names. I can
see how to select a certain character length, but until we can convince
everyone to have names of the same length, I can't see that helping. The
names are all stored with first name, a space, then last name.
Any help would be appreciated.
Thanks,
Brian
.
- References:
- Separating People
- From: Brian
- Re: Separating People
- From: Rick B
- Re: Separating People
- From: Rick B
- Separating People
- Prev by Date: Re: Separating People
- Next by Date: VBA Function that Identifies Significant Value Shifts
- Previous by thread: Re: Separating People
- Next by thread: Re: query on parameters
- Index(es):
Relevant Pages
|
