Code:
SELECT left([aname],
IIF(instr([aname], ",") = 0, InStr(1,[aname]," ")-1),InStr(1,[aname],",")-1)
& ", " &
right([aname],
IIF(instr([aname], ",") = 0,Len(aname)-InStr(1,[aname]," "),Len(aname)-InStr(1,[aname],",")
& " " &
defense_final.middle_initial AS fullname INTO FinalForgottenWithMiddle
FROM FinalForgotten INNER JOIN defense_final ON
((right(FinalForgotten.aname,IIF(instr([aname], ",") = 0,Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname]," ")),Len(FinalForgotten.aname)-InStr(1,FinalForgotten.[aname],","))=defense_final.first_name))
AND
((left(FinalForgotten.aname,,IIF(instr([aname], ",") = 0,InStr(1,FinalForgotten.[aname]," ")-1)),InStr(1,FinalForgotten.[aname],",")-1))=defense_final.last_name);
Thanks for response.