Access DELETE SURNAME FROM NAME LISTINGS under Access 2003

[Elgin.Marbles]

Using MSACCESS 2003.....
I WISH TO DELETE THE "SURNAME" OR THE LAST SEGMENT OF A NAME AND HAVE THE RESULT DISPLAYED IN A SINGLE FIELD.

ie .... ALBERT EDWARD WILLIAM GARDNER
Would become simply ALBERT EDWARD WILLIAM
...... WILLIAM GARDNER
Would become simply WILLIAM
...... CHRISTOPHER
Would remain as CHRISTOPHER

Within my list of names I have just Christian Names which need to remain as they are, and multiple forenames.

I have been trying to use the INSTR [INSTR (("[Name]"," ")] command but can only get this to detect the first space from the Left.... whereas I need to detect the first space from the Right OR no space detected.

The following [Left([Child], InStr(1, [Child], " ") - 1)] works well with fields that have a space in them but returns a RunTime error "5" on fields with a Single name in them (ie No Space)..

Can Anybody Help please ???

 

[Wolfgang]

I'm not that good with access, but maybe you can try to export the table of names to excel, deliminate it , then import it back to access. I'm sure it will work.

 

[Elgin.Marbles]

Wolfgang..... Thanks..... but I need to keep the solution within Access