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

magmike wrote 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

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

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

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

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

Phil

"magmike" wrote in message
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
>

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

On Oct 8, 4:02 pm, magmike wrote:
> 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)

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

On Oct 8, 3:48 pm, Bob Quintal wrote:
> magmike wrote 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 ?

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

On Oct 12, 9:31 am, magmike wrote:
> On Oct 8, 3:48 pm, Bob Quintal wrote:
>
>
>
>
>
> > magmike wrote 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 ?- Hide quoted text -
>
> - Show quoted text -

ABS( ) is a mistake. Instr( ) always returns a positive number, so
absolute value isn't needed. Speaking of mistakes... my solution
wouldn't work if there were middle names too... their solution
wouldn't work if there were titles or multi-part last names like "VAN
DAME". Since you always need the full last name, I would drop the
middle name and use my solution. Your choice.

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

On Oct 8, 5:02 pm, magmike wrote:
> 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)

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

> > > > 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.

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

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.

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

On Oct 12, 11:06 am, lyle wrote:
> On Oct 8, 5:02 pm, magmike wrote:
>
> > 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

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

On Oct 12, 11:06 am, lyle wrote:
> On Oct 8, 5:02 pm, magmike wrote:
>
> > 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?

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

On Oct 12, 3:06 pm, magmike wrote:
> On Oct 12, 11:06 am, lyle wrote:
>
> > On Oct 8, 5:02 pm, magmike wrote:
>
> > > 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?

Your use is likely to be determined by your need.

I would be inclined to create a Public Function as

Public Function Word(ByVal Phrase$, ByVal Ordinal&)
On Error Resume Next
Word = Split(Phrase)(Ordinal - 1)
End Function

and use it in a query as:

SELECT Word(Title,1) AS TitleOne
FROM Employees;

A Public Function puts you in control. In mine I have chosen to return
an empty string in cases where there is no word seven or three or
whatever. But someone else might choose to return the passed string.

We could write a Function to return Gordon from "Gordon G Todd" and
Gordon from "G Gordon Todd":
Public Function Word$(ByVal Phrase$, ByVal Ordinal&)
On Error Resume Next
Ordinal = Ordinal - 2
While Len(Word) < 2
Ordinal = Ordinal + 1
Word = Split(Phrase)(Ordinal)
Wend
End Function

but would that handle all cases, for instance, double word first
names? I don't think so.

But writing and running some functions like these might reduce the
time cost of doing a manual update.

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

On Oct 12, 10:31 am, magmike wrote:
> On Oct 8, 3:48 pm, Bob Quintal wrote:
>
>
>
> > magmike wrote 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.

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

On Oct 12, 11:54 am, OldPro wrote:
> On Oct 12, 9:31 am, magmike wrote:
>
>
>
> > On Oct 8, 3:48 pm, Bob Quintal wrote:
>
> > > magmike wrote 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 ?- Hide quoted text -
>
> > - Show quoted text -
>
> ABS( ) is a mistake. Instr( ) always returns a positive number, so
> absolute value isn't needed. Speaking of mistakes... my solution
> wouldn't work if there were middle names too... their solution
> wouldn't work if there were titles or multi-part last names like "VAN
> DAME". Since you always need the full last name, I would drop the
> middle name and use my solution. Your choice.

Instr can return a 0, which when we take away one gives a negative
number.

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

On Oct 12, 12:11 pm, OldPro wrote:
> > > > > 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.