Using RIGHT to obtain last word in string 1

[slames]

I have a database which has the whole name in one field, unfortunately the names don't just consist of first, middle and last names, they are more along the lines of The Revd Cannon Andrew Perry.
I am most concerned with the last part of the name as then at least I would be able to search on this bit. I am trying to use an update query to import the last bit of the string in to a new field. I can extract the first word using the left command and usiing mid I can get everything after the first space, but when I try to get the whole last word using the right command it returns only partial bits of it. This is my query:

UPDATE NewSubsTestWithNames SET NewSubsTestWithNames.sbNewSurname = Right$([sbLastName],InStr([sbLastName]," &quot);

Can any one point out where I'm going wrong please.
Thanks in Advance

Steph

 

[dodge20]

Try this You may want to back up your data first since it is an update

UPDATE NewSubsTestWithNames SET NewSubsTestWithNames.sbNewSurname = Right([sbLastName],Len([sbLastName]) -InStr([sbLastName]," &quot);


Dodge20

 

[slames]

This works fine if there are just 2 parts to the name but when there are more this is the result:
original field: THE REVD CANON DAVID AINSWORTH
new field: REVD CANON DAVID AINSWORTH
it still takes everything but the first word. I can't figure out why..

Thanks

 

[TonyJollans]

Hi slames,

Your problem is with your use of the InStr function. What it does is return the position, from the start, of the string of the first occurrence of the substring (space in your case). If your name is, to use your example, The Revd Cannon Andrew Perry, then your InStr will return 4 because the first space is in position 4 (after "The" taking the rightmost 4 characters will give you "erry" which is not what you want.

If you have Access 2K or newer there is another function, InStrRev. This will give you the position from the start of the last occurrence of the substring. Using the same example, InStrRev will return 23 representing the position of the space between "Andrew" and "Perry". Using Mid$ (you can use Right$ but it needs more complex calculations) from the next position should return what you want:

Mid$([sbLastName],InStrRev([sbLastName]," &quot+1)

If you have '97, you need a custom function which mimics InStrRev. There is one I have seen (by Bob Scriver, I think) posted somewhere on Tek-Tips but I don't have a reference to hand.

Enjoy,
Tony

 

[slames]

This sounds good, and now i understand why the previous version wasn't working - thanks. I am using Access 2000 but it gives the error "Undefined function instrrev" is there a way I can add this?

Thank You

Steph

 

[dodge20]

My brain is slow this morning. See if this works

UPDATE NewSubsTestWithNames SET NewSubsTestWithNames.sbNewSurname = Right(([sbLastName],InStr(sbLastName,' ')-1)


Dodge20

 

[dodge20]

I had an extra (

UPDATE NewSubsTestWithNames SET NewSubsTestWithNames.sbNewSurname = Right([sbLastName],InStr(sbLastName,' ')-1)

Dodge20

 

[slames]

Dodge20, unfortunately this gives me only some of the last name, but is not consistent at all, sometimes 3 letters of it sometimes 8, completely random.

STeph

 

[TonyJollans]

Hi slames,

Oh, dear! InStrRev is a VBA function, not directly available in a Query. You need to add a custom function.

In a Module:

Function MyInStrRev(MyString As String, MySubString As String) As Integer
    MyInStrRev = InStrRev(MyString,MySubString)
End Function

And then, in your Query:

Mid$([sbLastName],MyInStrRev([sbLastName]," &quot+1)

Having gone as far as creating a function, of course, you could make it do all the work for you if you wanted.

dodge20

Your code has the same problem as slames had. You are looking for the first space in the string, not the last.

Enjoy,
Tony

 

[slames]

TomyJollans - you are wonderful, thank you so much for your help. That worked perfectly.

Steph

 

[TonyJollans]

Hi Steph,

It's a pleasure

Thanks for the star.

Enjoy,
Tony