Extract Last Name from Name Field in Access

Follow the below expression for extracting last name

1) if Name field combination of First name and Last name only (no middle name) like Shashi Singh then
  extracting expression is Right(Trim([Names]),Len(Trim([Names]))-InStr(1, [Names]," "))

  Note: Instr() will find a specific string within a string, and will return the starting position. So in this case, you                want to find where that space is, and then return everything to the right of it. [string first character position            count as a 1 in Instr()]. For more details about Instr() visit 
                  http://office.microsoft.com/en-in/access-help/instr-function-HA001228857.aspx


2) if Name field combination of First name, Middle name and Last name only like Shashi Shankar Singh then
  extracting expression is
        Right(Trim([Names]),Len(Trim([Names]))-InStr(InStr(1, [Names]," ")+1,[Names]," "))

3) if Name field may have middle name or not eg: Shashi Singh or Shashi Shankar Singh then extracting expression is  IIf(InStr(InStr([names]," ")+1,[names]," ")<>0, Right([names],Len([names])-InStr(InStr([names]," ")+1, [names]," ")),Right([names],Len([names])-InStr([names]," ")))
 

Use in Update Query:
update <Table_name> set Caste=IIf(InStr(InStr(Trim([Relation_Name])," ")+1,Trim([Relation_Name])," ")<>0, ight(Trim([Relation_Name]),Len(Trim([Relation_Name]))- InStr(InStr(Trim([Relation_Name])," ")+1, Trim([Relation_Name])," ")), Right(Trim([Relation_Name]), Len(Trim([Relation_Name]))-InStr(Trim([Relation_Name])," ")))

Extracting First Name then expression is Left([Names],InStr(1,[Names]," ")-1)

Note: Since you do not want to include the space itself, we subtact 1 (one) from the result of the Instr function. Note this will return an #Error if it doesn't find a space, since Instr() returns 0 if string not found, and you cant take the Left Negative 1 characters. So this will work if EVERY record has a space in it.

Solution is LEFT([NameField], INSTR(1,[NameField] & " "," ")-1)

It will work for nulls, zero-length strings, and fields that don't contain a space, The trick is ensuring that the Instr can always find a space by adding a space to the end of the string.


Copied from http://support.microsoft.com/kb/286238 , http://www.pcreview.co.uk/forums/query-trim-name-field-t3978454.html