Easy one that I can't get my head round

[Geoff Codd]

I have a field which has records which all look like this

Resource Centre Electricity - Name of Department

What I want to do is strip out the bit containing Resource Centre Electricity leaving me with just the Name of Department

I know I need to use Instr and Right functions but I just can't get it to work.

Thanks in advance for your help
Geoff

 

[ColinEssex]

Update query?

Col

 

[Geoff Codd]

Why do I need to use an update query all I want to do is return a value of every thing to the right of the "- " so it can be used for a search criteria

 

[ColinEssex]

Oh right - I just thought you wanted to change it all in the table

 

[Rich]

Why do you have this as one field?

 

[Geoff Codd]

It's the way the database was originally set up and I can't change it

 

[Rich]

Try this
Function CutLastWord(S, Remainder)
'
' CutWord: returns the last word in S.
' Remainder: returns the rest
'
' Words are delimited by spaces.
'
Dim Temp, i As Integer, P As Integer
Temp = Trim(S)
P = 1
For i = Len(Temp) To 1 Step -1
If Mid(Temp, i, 1) = " " Then
P = i + 1
Exit For
End If
Next i
If P = 1 Then
CutLastWord = Temp
Remainder = Null
Else
CutLastWord = Mid(Temp, P)
Remainder = Trim(left(Temp, P - 1))
End If
End Function

 

[Geoff Codd]

Thanks Rich,

But I got the following to work, I think it's a lot more straight forward

Right(Trim([Lookup_Name]),Len(Trim([Lookup_Name]))-InStr(1,[Lookup_Name],"- ")-1)

Thanks
Geoff