Text Trim function

[pjk]

can't seem to locate this in search - so probably not searching in right place.
I am trying to take name field - and split off last name and place in new
field.

for example:

John Smith Smith
John Kennedy Kennedy

i have seen it done - but can't quite get the function right - any help
would be apprectiated.
thanks

 

[BruceM]

For the last name, in a blank column in query design view you could do:
LastName: Right([NameField],InStr([NameField]," "))

For the first name:
FirstName: Left([NameField],InStr([NameField]," ") - 1)

You could also use the expressions as the Control Source of unbound text
boxes on a form or report:
=Right([NameField],InStr([NameField]," "))
=Left([NameField],InStr([NameField]," ") - 1)

This will work only if the names are in the exact format you showed. If
there are three or more names you will get the correct result only by
chance. If possible you should parse the names as shown, or by whatever
method works, then use an update query to modify the table.

 

[Wayne-I-M]

In query


SELECT Left([YourTable]![FullNameField],InStr([YourTable]![FullNameField],"
")-1) AS NewFrstName,
Mid([YourTable]![FullNameField],InStr([YourTable]![FullNameField]," ")+1) AS
NewSurname
FROM YourTable;


Or
Left([YourTable]![FullNameField],InStr([YourTable]![FullNameField]," ")-1)
and
Mid([YourTable]![FullNameField],InStr([YourTable]![FullNameField]," ")+1)



Change [YourTable]![FullNameField] to what it really is

Good luck

 

[KARL DEWEY]

Also if the last name is like 'de la Garza' or Von Sleigher' the you have a
problem.

--
KARL DEWEY
Build a little - Test a little

For the last name, in a blank column in query design view you could do:
LastName: Right([NameField],InStr([NameField]," "))

For the first name:
FirstName: Left([NameField],InStr([NameField]," ") - 1)

You could also use the expressions as the Control Source of unbound text
boxes on a form or report:
=Right([NameField],InStr([NameField]," "))
=Left([NameField],InStr([NameField]," ") - 1)

This will work only if the names are in the exact format you showed. If
there are three or more names you will get the correct result only by
chance. If possible you should parse the names as shown, or by whatever
method works, then use an update query to modify the table.

can't seem to locate this in search - so probably not searching in right
place.
I am trying to take name field - and split off last name and place in new
field.

for example:

John Smith Smith
John Kennedy Kennedy

i have seen it done - but can't quite get the function right - any help
would be apprectiated.
thanks

 

[Klatuu]

The good news is BruceM shows a very easy example.
The bad news is you will never get it working propery. Trying to parse
names is easier than making it throught the Twilight Zone. There are too
many variations and too many ways data entry people can put names in to ever
get it 100%.

So, be prepared to audit your results carefully. Hopefully this is a one
time shot to improve your database design.

 

[pjk]

So It sounds like the best thing is to split the 1st and last names into 2
separate fields for data entry and then combine in report format if needed
for reporting.

thanks
pjk

 

[Klatuu]

Exactly! that is the way it should be done. Spliting is almost impossible,
but combining is pretty easy.

What is just as bad if not worse than full names in one field is a full
address in one field. I once worked on a database where we received
addresses with the street number and name in one field. We had to break it
out to determine if the street number was within a valid range, then
translate that to a postal route code (this was before GPS was available -
1986), and determine to which store to route the order. We used the USPS
naming standards because our database was originally loaded from their
address data. There are certain valid street prefixes, suffixes and road
types(St, Street, Rd, Road, Bldv, Ave, Ln, etc), and directions like N,S,E,W
or North, East, West, South, NW, etc. So the algorythm we devised started
from both ends and worked toward the middle breaking the text out. Worked
pretty well, but it testing we hit some bumps. The one I still remember is
4000 West Loop South

Okay, the number is 4000, got it.
South is a directional Suffix, got it.
West is a directioal Prefix, got it.
Loop is a Street type, got it.
So where is the street name?
The name of the street was actually West Loop.

 

[pjk]

thanks much

Exactly! that is the way it should be done. Spliting is almost impossible,
but combining is pretty easy.

What is just as bad if not worse than full names in one field is a full
address in one field. I once worked on a database where we received
addresses with the street number and name in one field. We had to break it
out to determine if the street number was within a valid range, then
translate that to a postal route code (this was before GPS was available -
1986), and determine to which store to route the order. We used the USPS
naming standards because our database was originally loaded from their
address data. There are certain valid street prefixes, suffixes and road
types(St, Street, Rd, Road, Bldv, Ave, Ln, etc), and directions like N,S,E,W
or North, East, West, South, NW, etc. So the algorythm we devised started
from both ends and worked toward the middle breaking the text out. Worked
pretty well, but it testing we hit some bumps. The one I still remember is
4000 West Loop South

Okay, the number is 4000, got it.
South is a directional Suffix, got it.
West is a directioal Prefix, got it.
Loop is a Street type, got it.
So where is the street name?
The name of the street was actually West Loop.