as, suggested you can make a function using split():
Public Function fnLast2Words(ByVal sp As String) As String
sp = sp & ""
If Len(sp) Then
Do Until InStr(1, sp, " ") = 0
sp = Replace$(sp, " ", " ")
var = Split(sp, " ")
If UBound(var) > 0 Then
fnLast2Words = Trim$(var(UBound(var) - 1)) & " " & Trim$(var(UBound(var)))
select *, fnLast2Words([fieldName]) As Last2 from yourtable;
Concatenating the search character onto the end (or beginning in the case of InStrRev) of the searched string avoids both these problems.
The advantage of using engine functions over user defined functions is performance while the advantage of the UDF is the ability to include refinements like dealing with double spaces as Arnelgp has demonstrated.
BTW A Null would error out his function too because its input parameter is String. It could be substituted with Variant but it would be less efficient.