Right Function

[Ken]

Hi,
I have a table with data like the below:

Mr Bill Parnell
Mr John Dough
Ms Virginia Hough

My RIGHT FUNCTION statement is this:

Table1![FName] = Right(Table1![Name], InStr(Table1![Name], " "))

When I run this statement, I get the following with different variations:

nell
ugh
ough

I'm trying to use this RIGHT function to capture the data (LName) which
would be from the right side of the data up to the first blank (blank between
first name and last name. Can someone help me correct this statement? Output
should look like this:
Parnell
Dough
Hough

Thanks in advance.

 

[Jeff Boyce]

Ken

Re-read the syntax explanation for Left(), Mid(), and Right() in Access
HELP. Your syntax told Access to take the rightmost "n" characters, where
"n" = the position where the first blank occurs (i.e., after Mr, Ms, ...).

You may need to parse this in two steps: first, to get rid of the
Mr/Ms/..., second to split FName and LName.

By the way, your syntax also says you are creating a FName ... but I suspect
you were trying to get the LName.

 

[Ken]

Hi Jeff,
I'm trying to extract the last name using the RIGHT() function by looking
backwards in the string for the first blank space, but as you can see
something is not exactly right cause all I'm getting is a few of the
characters (in most cases only 4 characters of last name and in others more
characters). If I change the RIGHT in the statement to LEFT it works just
fine, i.e. it extracts the first name up to the first blank every time. I
understand something is wrong, but can't figure it out.

Ken

Re-read the syntax explanation for Left(), Mid(), and Right() in Access
HELP. Your syntax told Access to take the rightmost "n" characters, where
"n" = the position where the first blank occurs (i.e., after Mr, Ms, ...).

You may need to parse this in two steps: first, to get rid of the
Mr/Ms/..., second to split FName and LName.

By the way, your syntax also says you are creating a FName ... but I suspect
you were trying to get the LName.

--
Good luck

Jeff Boyce
<Access MVP>

Hi,
I have a table with data like the below:

Mr Bill Parnell
Mr John Dough
Ms Virginia Hough

My RIGHT FUNCTION statement is this:

Table1![FName] = Right(Table1![Name], InStr(Table1![Name], " "))

When I run this statement, I get the following with different variations:

nell
ugh
ough

I'm trying to use this RIGHT function to capture the data (LName) which
would be from the right side of the data up to the first blank (blank between
first name and last name. Can someone help me correct this statement? Output
should look like this:
Parnell
Dough
Hough

Thanks in advance.

 

[Ian Logan]

You want to use:

Table1![FName] = Right(Table1![Name], LEN(Table1![Name]) -
InStr(Table1![Name], " ") - 1)

What was happening was that the INSTR was pointing to the space, but
pointing from the LEFT and not the RIGHT. Hence for a string "Joe Bloggs"
then INSTR gives 4 and so your calculation yields "oggs". Using the LEN
function gives the correct result (less 1 to take account of the space).
Note that this will still fall down if the Name has more than one space e.g.
Mr Joe Bloggs or Joe Big Bloggs. You should either use InStrRev or look for
the last occurance of a space in the Name string.

Trust this helps.

Ian L

Ken

Re-read the syntax explanation for Left(), Mid(), and Right() in Access
HELP. Your syntax told Access to take the rightmost "n" characters, where
"n" = the position where the first blank occurs (i.e., after Mr, Ms, ...).

You may need to parse this in two steps: first, to get rid of the
Mr/Ms/..., second to split FName and LName.

By the way, your syntax also says you are creating a FName ... but I suspect
you were trying to get the LName.

--
Good luck

Jeff Boyce
<Access MVP>

Hi,
I have a table with data like the below:

Mr Bill Parnell
Mr John Dough
Ms Virginia Hough

My RIGHT FUNCTION statement is this:

Table1![FName] = Right(Table1![Name], InStr(Table1![Name], " "))

When I run this statement, I get the following with different variations:

nell
ugh
ough

I'm trying to use this RIGHT function to capture the data (LName) which
would be from the right side of the data up to the first blank (blank between
first name and last name. Can someone help me correct this statement? Output
should look like this:
Parnell
Dough
Hough

Thanks in advance.

 

[Jeff Boyce]

Ken

As Ian points out (and you would find re-checking HELP), the Right()
function returns the right-most "n" characters. The InStr() function starts
counting at the LEFT.

Good luck

Jeff Boyce
<Access MVP>

Hi Jeff,
I'm trying to extract the last name using the RIGHT() function by looking
backwards in the string for the first blank space, but as you can see
something is not exactly right cause all I'm getting is a few of the
characters (in most cases only 4 characters of last name and in others more
characters). If I change the RIGHT in the statement to LEFT it works just
fine, i.e. it extracts the first name up to the first blank every time. I
understand something is wrong, but can't figure it out.

Ken

Re-read the syntax explanation for Left(), Mid(), and Right() in Access
HELP. Your syntax told Access to take the rightmost "n" characters, where
"n" = the position where the first blank occurs (i.e., after Mr, Ms, ....).

You may need to parse this in two steps: first, to get rid of the
Mr/Ms/..., second to split FName and LName.

By the way, your syntax also says you are creating a FName ... but I suspect
you were trying to get the LName.

--
Good luck

Jeff Boyce
<Access MVP>

Hi,
I have a table with data like the below:

Mr Bill Parnell
Mr John Dough
Ms Virginia Hough

My RIGHT FUNCTION statement is this:

Table1![FName] = Right(Table1![Name], InStr(Table1![Name], " "))

When I run this statement, I get the following with different variations:

nell
ugh
ough

I'm trying to use this RIGHT function to capture the data (LName) which
would be from the right side of the data up to the first blank (blank between
first name and last name. Can someone help me correct this statement? Output
should look like this:
Parnell
Dough
Hough

Thanks in advance.