I've tried to correct an issue for some import of last names from an Excel sheet that has names like O'Brien or D'Angelo listed as "O Brien" or "D Angelo". Unfortunately, that's how the list comes to me. I tried to use an update query but sometimes it gives the wrong name such as O'ien or O'onnor where it deleted the B or C in the last name. Here's the update query.

UPDATE dbo_Contacts
SET
    dbo_Contacts.[Last Name] = Left([Last Name], 1) & "'" & StrConv (Mid ([Last Name], 4), 3)
WHERE
    (
        ((Mid ([last name], 2, 1)) = " ")
        AND ((Left([Last Name], 1)) = "o")
    )
    OR (
        ((Mid ([last name], 2, 1)) = " ")
        AND ((Left([Last Name], 1)) = "d")
    );

And yes, this is before I rebuilt my database with better field names. :)

Any help would be appreciated.