Manipulating names

[Jim Jones]

I have a table in which student names are captured as surname followed by
first names. I then rotate the names so that Jones Jim becomes Jim Jones. I
do so by using the following code:

Function Invert_Name(nam)
sep = InStr(1, nam, " ")
If sep = 0 Then
Invert_Name = nam
Exit Function
End If
fnam= Right(nam, Len(nam) - sep)
lnam = Left(nam, sep - 1)
Invert_Name = fname & " " & lnam
End Function

This works fine and Jones Jim becomes Jim Jones. However, the it does not
work when there are more than one first name - so Jones Jim Edward does not
rotate. How can I amend the code so that the rotation takes place when the
first space is reached, whilst any other subsequent spaces just become part
of the fist name string?

Any help would be much appreciated.

Jim Jones
Ministry of Education
Botswana

 

[Dennis]

Why does this not work ? Your instr function with a 1 as the first parameter
means start from the first character so it will always find the first space.
Therefore looking at your code, Jones Jim Edward should return
Jim Edward Jones. Is this correct ?

 

[Jim Jones]

Dennis

Many thanks for your prompt response. Thank you also for confirming the
accuracy of the code. Since receiving your reply, I have found instances of
where the code is doing exactly as we expect. So, I think something else is
going on which I will now investigate.

So, I don't think the Group can help me further until I have done more work
here.

Thanks again

Jim Jones