Right() String question

[cyberfly]

I need to extract the last word from a field in a query, however, I am at a loss on how to count to a space from the right of the string.

Mr John Smith & Mrs Mary Jones

Above is an example of the string and I would basically like to know how to extract Jones. As the last word will be of variable length I know that right(fieldname,5) will not be sufficient.

Thank you very much

 

[Liv Manto]

Use instr()


Ex:

1234567
ABCD_FG

x= ABCD_FG


Pretend the _ is space. Space is on the fifth of the term (x).

Instr(x, "_") will give you 5. Add 1 to 5 and you got the beginning of the second word
"FG"

So now you can use MId(x, (instr(x,"_")+1))

Just replace "_" with " "

 

[R. Hicks]

Hmmmm.....

Here is a function I quckly wrote. I have only done limited testing on it. See if this will help get the results you need.

Public Function fGetLastWord(strEntry As Variant)
Dim strTemp As String
Dim x As Integer
If IsNull(strEntry) Then Exit Function

For x = Len(strEntry) To 1 Step -1
If Mid(strEntry, x, 1) = " " Then
strTemp = Mid(strEntry, x + 1)
Exit For
End If
Next x

fGetLastWord = strTemp

End Function

HTH
RDH