Cut last name from a string

Hi, I have a table with a customer's complete name ie: Fran McLellan & Rueben Grue. I need to get just the last name separtated from this field and then strip the rest out for the first name. I have used this in a query to try and do this:
Lname: Mid([letnam],InStr([letnam]," ")+1,Len([letnam])) this returns: McLellan & Rueben Grue. To get the first name out I used:
Fname: Left([letnam],InStr([letnam]," ")-1) and get Fran returned. What I need to see is Fran McLellan & Rueben for the first name and Grue for the last name. Thanks for any help in advance.

From your example, it appears you need a means of finding the location of the last space in the string. Try copying/pasting this code into a new module, then, to test from the debug window:

widget = "Fran McLellan & Rueben Grue"
fname = left(widget, xlastinstr(widget," ")-1)
lname = mid(widget, xlastinstr(widget," ")+1)
? fname
Fran McLellan & Rueben
? lname
Grue


' FUNCTION: xLastInStr()
'
' PURPOSE: Determine the position of the last character(s)
' (as specified by user) in a string.
'
' ARGUMENTS:
' tstr: The string to be tested
' twhat: The character to locate.
'
' RETURNS: An integer representing the last occurence or, if not found, 0.
'
' NOTES: To test: Type '? xLastInStr("The quick brown fox jumped over
' the lazy dog", "the") in the debug window.
' The function will return 33.

Function xLastInStr(ByVal tstr As String, twhat As String) As Integer
Dim i As Integer, n As Integer, tlen As Integer

n = 0
tlen = Len(twhat)
For i = Len(RTrim(tstr)) To 1 Step -1

If Mid(tstr, i, tlen) = twhat Then
n = i
Exit For
End If
Next i

xLastInStr = n

End Function