Query to remove middle initial from name

[MARIA]

My table contains employee names some with middle initial,
some without: "SMITH, BOB K"

I need to get rid of the middle initial and space. Can
anyone help me? I believe I need to use a LEFT or RIGHT
but am not sure.

thanks.

 

[Phobos]

If you want an UPDATE query to make the changes permanent:

UPDATE MyTable SET MyTable.MyField = Left([MyField],Len([MyField])-2)
WHERE ((Left(Right([MyField],2),1)=" "));

Or if you want a calculated field in a SELECT query:

SELECT IIf(Left(Right([MyField],2),1)="
",Left([MyField],Len([MyField])-2),[MyField]) AS NewField
FROM MyTable;

P