Strange output from Right function

[Kevin Beck via AccessMonster.com]

Mornin' evry1,

I have been struggling with this for a few days,

I am having trouble getting the right results from the Right function. I am
simply sparsing out a name column using right, mid, and left functions. All
is working fine but the right function is giving strange output. the name
field looks like this:

First
-----
Miller Jr, John
Miller Jr, Albert

I am trying to get:

John
Albert

the function I'm using is

Right([First],InStr([First]," ",)-1)

And I'm getting

, John
Albert

Or sometimes something like:

ller Jr, John
ler Jr, Albert

Help much appreciated, thanks

Kevin

 

[JohnFol]

But that is correct.
If the data is "Miller Jr, John", and your expression is
Right([First],InStr([First]," ",)-1)
then InStr([First]," ") should return 7 as it is looking for the first space
from the LEFT

If you then return the 6 characters (7-1) counting from the RIGHT of the
string you get
", John"

PS
You have InStr([First]," ",) it should be InStr([First]," ")


What do you want to return?

 

[Brian]

Mornin' evry1,

I have been struggling with this for a few days,

I am having trouble getting the right results from the Right function. I am
simply sparsing out a name column using right, mid, and left functions. All
is working fine but the right function is giving strange output. the name
field looks like this:

First
-----
Miller Jr, John
Miller Jr, Albert

I am trying to get:

John
Albert

the function I'm using is

Right([First],InStr([First]," ",)-1)

And I'm getting

, John
Albert

Or sometimes something like:

ller Jr, John
ler Jr, Albert

Help much appreciated, thanks

Kevin

To take "Miller Jr, John" as an example:

InStr([First]," ",) will return 7 (the first space in the string). So,
InStr([First]," ",)-1 is 6. Right([First], 6) returns ", John", the
rightmost 6 characters.

InStr counts from the start of the string, Right counts from the end of the
string. Try using InStrRev instead.

 

[Marshall Barton]

I am having trouble getting the right results from the Right function. I am
simply sparsing out a name column using right, mid, and left functions. All
is working fine but the right function is giving strange output. the name
field looks like this:

First
-----
Miller Jr, John
Miller Jr, Albert

I am trying to get:

John
Albert

the function I'm using is

Right([First],InStr([First]," ",)-1)

Just use:
Mid([First], Instr([First], ",") + 2)

 

[MGFoster]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You're using Right & InStr incorrectly. InStr() returns the position of
the 1st character in the search string. The 2nd parameter in Right() is
the number of characters to retrieve from the right side of the string.
What you need to do is perform a calculation to determine how many
characters are to the right of the comma-space delimiter between last
name and first name. E.g.:

strFirstName = Right([First],Len([First]) - InStr([First],", ")-1)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQmKVkoechKqOuFEgEQI4cgCfb3Rz+w7xYkmXen1ThKIb26UOXqcAoMQ2
tZeecKv0ta+QgS4Uah6nhhjx
=vDPh
-----END PGP SIGNATURE-----