ACC2: Sample Function to Obtain the Last Word in a String (117914)

SUMMARY

This article demonstrates a sample user-defined Access Basic function that you can use to return the last word in any text string.

This article assumes that you are familiar with Access Basic and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information on Access Basic, please refer to the "Building Applications" manual.

MORE INFORMATION

You can use the LastWord() function to return the last word in a string. For example, the LastWord() function will return "Doe" from the string "Mr. and Mrs. John Doe."

You can also use the LastWord() function in your application to sort a list of names by last name, if the first and last names are in the same field, or if the middle names are included with the first names in a field.

The LastWord() Function

   Function LastWord (anystring)
      If Not InStr(anystring, Chr(32)) = 0 Then
         Dim FindSpace As String, Pos As Integer, StrLen As Integer
         FindSpace = "Z"
         StrLen = Len(anystring)
         Pos = StrLen
         Do Until FindSpace = Chr(32)
            Pos = Pos - 1
            FindSpace = Mid(anystring, Pos, 1)
         Loop
         LastWord = Mid(anystring, Pos + 1, StrLen - Pos + 1)
      End If
   End Function

REFERENCES

For additional information about parsing strings, please see the following article in the Microsoft Knowledge Base:

99405 ACC: Part 2 DDE in Visual Basic to Request Data from MS Access