using LEFT to select a whole word (the left-most one)

[magmike]

I am trying to use this with a name field that has the entire name in
one field in a report that is actually a letter. In the letter, of
course, I need to address the recipient by first name in the greeting
line. Can I create a statement to use only the left-most word instead
of stating how many characters?

Many, many thanks!

mike

[Phil Stanton]

Left(PersonsWholeName ,Instr(PersonsWholeName," "))
Searches for the first space

Phil

"magmike" <ma******@yahoo.comwrote in message
news:11**********************@g4g2000hsf.googlegro ups.com...

>I am trying to use this with a name field that has the entire name in
one field in a report that is actually a letter. In the letter, of
course, I need to address the recipient by first name in the greeting
line. Can I create a statement to use only the left-most word instead
of stating how many characters?

Many, many thanks!

mike

[Bob Quintal]

magmike <ma******@yahoo.comwrote in news:1191877329.697545.220570
@g4g2000hsf.googlegroups.com:

I am trying to use this with a name field that has the entire name in
one field in a report that is actually a letter. In the letter, of
course, I need to address the recipient by first name in the greeting
line. Can I create a statement to use only the left-most word instead
of stating how many characters?

Many, many thanks!

mike

Use the instr() function to find the first space, subtract 1, test for
negative value and pass this to the left() function

FirstName: left(Fullname,abs(instr(Fullname," ")-1))

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

[OldPro]

On Oct 8, 4:02 pm, magmike <[e-mail]:

I am trying to use this with a name field that has the entire name in
one field in a report that is actually a letter. In the letter, of
course, I need to address the recipient by first name in the greeting
line. Can I create a statement to use only the left-most word instead
of stating how many characters?

Many, many thanks!

mike

Assuming that the first name is always last, and that it is separated
with a space, and that it will never have any trailing spaces, then
use instrRev( ) to locate the last space. Your first name will start
in the next position.
sFirstName=mid(instrRev("Smith, John"," ")+1)

[magmike]

On Oct 8, 3:48 pm, Bob Quintal <[e-mail]:

magmike <[e-mail] in news:1191877329.697545.220570
@g4g2000hsf.googlegroups.com:

I am trying to use this with a name field that has the entire name in
one field in a report that is actually a letter. In the letter, of
course, I need to address the recipient by first name in the greeting
line. Can I create a statement to use only the left-most word instead
of stating how many characters?

Many, many thanks!

mike

Use the instr() function to find the first space, subtract 1, test for
negative value and pass this to the left() function

FirstName: left(Fullname,abs(instr(Fullname," ")-1))

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account fromhttp://www.teranews.com

and what is the abs for ?

[lyle]

On Oct 8, 5:02 pm, magmike <[e-mail]:

I am trying to use this with a name field that has the entire name in
one field in a report that is actually a letter. In the letter, of
course, I need to address the recipient by first name in the greeting
line. Can I create a statement to use only the left-most word instead
of stating how many characters?

Many, many thanks!

mike

If you are using Access>=2000 you might try:
Split(EntireName)(0)

[OldPro]

I am trying to use this with a name field that has the entire name in

one field in a report that is actually a letter. In the letter, of
course, I need to address the recipient by first name in the greeting
line. Can I create a statement to use only the left-most word instead
of stating how many characters?

Mea culpa. I misread you initial statement thinking your names were
last name, comma, space, first name format. Bob's example should work
fine without the ABS( ) unless there is an accidental space before the
first name (this happens occasionally) or there is no space at all, in
which case you would get an nasty error message.

[magmike]

When I have time, I am going to seperate the name field out by first,
middle, last, suffix, etc. I also like to add a field for
pronunciation, and a "Go By" field, for when some guy named george
goes by Skip, or Scooter. That way, when you have a first name like
Mary Ann, or a last name like Van Damme, you don't have to worry about
how the left thing will come out. But, I inherited this thing and
needed a temporary fix until that can happen.

[magmike]

On Oct 12, 11:06 am, lyle <[e-mail]:

On Oct 8, 5:02 pm, magmike <[e-mail]:

I am trying to use this with a name field that has the entire name in
one field in a report that is actually a letter. In the letter, of
course, I need to address the recipient by first name in the greeting
line. Can I create a statement to use only the left-most word instead
of stating how many characters?

Many, many thanks!

mike

If you are using Access>=2000 you might try:
Split(EntireName)(0)

How does that come out? How does it effect double first names (like
Mary Ann), double last names (like Van Damme), hyphenated last names
(like Evans-Schelske), the presence of middle names and/or initialed
names (like P. Thomas Jenkins or Alfred E. Newman)?

mike

[magmike]

On Oct 12, 11:06 am, lyle <[e-mail]:

On Oct 8, 5:02 pm, magmike <[e-mail]:

I am trying to use this with a name field that has the entire name in
one field in a report that is actually a letter. In the letter, of
course, I need to address the recipient by first name in the greeting
line. Can I create a statement to use only the left-most word instead
of stating how many characters?

Many, many thanks!

mike

If you are using Access>=2000 you might try:
Split(EntireName)(0)

And... how would I use that? What is the output? I don't think I could
use that as a query field. Is that some sort of special query that
creates a table or just gives specialized results of a table?

[rquintal]

On Oct 12, 10:31 am, magmike <[e-mail]:

On Oct 8, 3:48 pm, Bob Quintal <[e-mail]:

magmike <[e-mail] in news:1191877329.697545.220570
@g4g2000hsf.googlegroups.com:

I am trying to use this with a name field that has the entire name in
one field in a report that is actually a letter. In the letter, of
course, I need to address the recipient by first name in the greeting
line. Can I create a statement to use only the left-most word instead
of stating how many characters?

Many, many thanks!

mike

Use the instr() function to find the first space, subtract 1, test for
negative value and pass this to the left() function

FirstName: left(Fullname,abs(instr(Fullname," ")-1))

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account fromhttp://www.teranews.com

and what is the abs for ?

If there is no space at all in the field, instr() returns 0,
from which we are removing 1. In that case the -1
result will trigger an error message from left(). The abs() converts -
1 to +1,which will return the space or first initial, a better choice
in my opinion, than an error message.

One could move the code into a user-defined function where one could
build more sophisticated error checking and correction, but this is
quick, dirty and works.

[rquintal]

On Oct 12, 12:11 pm, OldPro <[e-mail]:

I am trying to use this with a name field that has the entire name in
one field in a report that is actually a letter. In the letter, of
course, I need to address the recipient by first name in the greeting
line. Can I create a statement to use only the left-most word instead
of stating how many characters?

Mea culpa. I misread you initial statement thinking your names were
last name, comma, space, first name format. Bob's example should work
fine without the ABS( ) unless there is an accidental space before the
first name (this happens occasionally) or there is no space at all, in
which case you would get an nasty error message.

the abs() prevents getting an error message.