String Search Challenge

[jkfeagle]

OK I'm throwing a challenge out there to everyone. I am overloaded and trying to figure this out is driving me nuts. What I have is a table with a list of names as such:

James Smith
Susan E Jones
Gary Duane Thompson
etc.
etc.

Note the variability in how much of the name is given. What I need to do is find a way to change every one of these names to the following format either through a query or code:

Smith James
Jones Susan E
Thompson Gary Duane

Can anybody help me out here?

 

[pbaldy]

It could get a lot more complicated, but based on what you've posted it's not too difficult. You could use the InStrRev function to find the last space. Use that with the Mid function to get the last name, and again with the Mid function to get the rest (using it in different arguments each time).

 

[DCrake]

Simple Software Solutions

Public Function ReverseName(Optional AnyName As String) As String

'Purpose:Reverse the surname and forename(s) in any given string
'           :using the final space as the delimiter (if found)
'           :Converts the incoming string to Proper format - First Letter Captialisation
'Example :Peter JONES Return Jones Peter
'            :Nigel Twiston-Davis Returns Twiston-David Nigel
'If more than one space is found in name then it will split the surname into 2 



If IsMissing(AnyName) Or IsEmpty(AnyName) Then
   Exit Function
End If



Dim LastWordInString As String
Dim OtherWordsInString As String
Dim IntPosOfSpace As Integer

IntPosOfSpace = InStrRev(AnyName," ")

If IntPosOfSpace = 0 Then
    ReverseName = StrConv(AnyString,3)
Else
    ReverseName = StrConv(Mid(AnyName,IntPosOfString+1) & " " & Left(AnyName,IntPosOfString-1),3)
End If

End Function

CodeMaster: