query for extracting charechters from a field

[ob_ghosh]

thanx for all the help...but i have another slight problem.
i have used this expression to identify the last space in the description field

Expr1: IIf(Mid([description],Len([description])-1,1)=" ",1,IIf(Mid([description],Len([description])-2,1)=" ",2,3))

then i have managed to extract the charechters to the right of the last space using (ie the chars following the last space)

Expr2: Right([description],[expr1])

but now i require to extract the charechter immediately preceeding the last space, and then what remains i am required to put in the next field ( ie the 'description' without the chars immediately preceeding and the last space and following the last space)
so can anyone help me with the expressions
i have tried using mid and left rather unsuccessfully so if anyone could help

 

[raskew]

Try copying this function to a module:

'***************************************

Function xLastInStr(ByVal tstr As String, twhat As String) As Integer
'*******************************************
'Name: xLastInStr (Function)
'Purpose: Return location of last instance of a character or phrase.
'Author: raskew
'Inputs: Call xLastInStr("the quick brown fox jumped the lazy dog", "the")
'Output: 28 - Location of last occurence of "the"
'*******************************************

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

'***************************************

To test its usage, try the following from the debug window:

widget = "The quick brown fox jumped over the lazy dog"
leftStuff = left(widget, xlastinstr(widget, " ")-1)
rightStuff = mid(widget, xlastinstr(widget, " ")+1)
? leftstuff
The quick brown fox jumped over the lazy
? rightstuff
dog