get last word of a string

[Chris Cowles]

Can someone post the entire content of a formula or module that will parse a
string and return only the last word? I know it's some to do with a 'do
while' loop, but am not familiar enough with it to write it myself from
scratch. Basically, I want it to search a string for the last space, then
return the remaining text, trimmed.

I'm assuming that if it's a module the column heading will be something
like:

lastword: function(

![fieldname])

TIA for any assistance.

Chris Cowles,
Gainesville, FL

 

[Pat Garard]

'Generalised Solution
Sub Test()
Dim strMyString As String
Dim intMyPosition As Integer
strMyString = "This is a test"
Do
strMyString = InputBox("TestString:", "Test", "This is a test")
intMyPosition = InStrRev(strMyString, " ")
strMyString = Right(strMyString, Len(strMyString) - intMyPosition)
MsgBox (strMyString)
Loop While strMyString <> "Quit"
End Sub
--
Hope this helps!

Pat Garard
Australia.
apgarardATbigpondDOTnetDOTau

 

[HSalim]

and here is the specific solution - using Pat's code

Function LastWord(ByVal strInput As String) As String
Dim intMyPosition As Integer
' Remove trailing spaces if any
strInput = Trim(strInput)
'find position of last space
intMyPosition = InStrRev(strInput, " ")
'get everything to the right of the space
LastWord = Right(strInput, Len(strInput) - intMyPosition)
End Function

 

[Van T. Dinh]

If you use A2K or AXP, then the expression:

Mid(YourString, InStrRev(YourString, " ") + 1)

should give the last word. For example (from A2K Debug
window:

MyString$ = "This is a test string"

?Mid(MyString, InStrRev(MyString, " ") + 1)
string <--- returned value

HTH
Van T. Dinh
MVP (Access)

 

[HSalim]

Chris,
There's got to be something else that's wrong. I just checked ACC97 and the
function is there alright.
It has been there since the early versions of VB and VBScript.

What is the error message?

HS

 

[Chris Cowles]

Using 'build' in a query and, in expression builder, selecting functions:
built-in functions: text: the list of available functions does include InStr
but does not include InStrRev. I'm not at the computer where I created the
module that reported the error, but the error was something to the effect of
'unknown function. I highlighted 'InStrRev' in the display, so I believe I
entered your logic correctly but it just didn't recognize the verb.

I'll look at this at work later today and provide the specific message.

I'm using ACC97 SR-2. Must I have installed any VB extras, or something? I
can do that if necessary.

Thanks for your patience.

 

[Brendan Reynolds \(MVP\)]

I don't have Access 97 installed on this PC, but I'm about 99.99% certain
! that InStrRev was new in Access 2000.

 

[HSalim]

Brendan, You are right. Chris, set a reference to VBScript and you'll be
set.
Regards
HS

Alternatively, you could use this code:
Function LastWord(ByVal strInput As String) As String
Dim i As Integer
dim strOut As String

' Remove trailing spaces if any
strInput = Trim(strInput)

'use a loop to start from the end and move back
For i = Len(strInput) To 1 Step -1
'if current char is a space, end the loop
If Mid(strInput, i, 1) = " " Then
Exit For
Else
'Add new char before the output
strOut = Mid(strInput, i, 1) & strOut
End If

'Debug.Print strOut 'used for testing
Next

LastWord = strOut

End Function

 

[Chris Cowles]

That did the trick! Thanks to all for your input.

-----Original Message-----
Brendan, You are right. Chris, set a reference to VBScript and you'll be
set.
Regards
HS

Alternatively, you could use this code:
Function LastWord(ByVal strInput As String) As String
Dim i As Integer
dim strOut As String

' Remove trailing spaces if any
strInput = Trim(strInput)

'use a loop to start from the end and move back
For i = Len(strInput) To 1 Step -1
'if current char is a space, end the loop
If Mid(strInput, i, 1) = " " Then
Exit For
Else
'Add new char before the output
strOut = Mid(strInput, i, 1) & strOut
End If

'Debug.Print strOut 'used for testing
Next

LastWord = strOut

End Function


selecting computer where I created
the something to the
effect display, so I believe
I extras, or something?
I just checked ACC97
and


.