Query with InStr Function.

[furnitureheaven]

Hi
i have a table with first name and Middle name in same field(length of names is variable). like

Derake
Simon K.
Ak D.
Paul LM.
Dixxions DYJ.

etc. i want to split them into two columns, so after space it should split the first name and middle name separately. like

FirstName........ MidName
Derake ........
Simon ........ K.
AK ........ D.
Paul ........ LM.
Dixxions ........ DYJ

for this purpose i have write a querry, but shows the result like that,

Fore Name .......FirstName........ MidName
Derrak .......#Error ....... #Error
Akal .......#Error ....... #Error
Simon K. .......Simon ....... K.
Ak D. .......AK ....... D.
Paul LM. .......Paul ....... LM.
Dixxions DYJ. .......Dixxions ....... DYJ.

The Query is like this.

[FONT=Times New Roman][SIZE=3][SIZE=3][FONT=Times New Roman]SELECT Samp.Forename, Left([Samp.Forename],InStr(1,[Samp.Forename], ' ') -1) AS Fname,  mid(Samp.Forename,instr(Samp.Forename," ") )  AS MidName[/FONT][/SIZE]
[SIZE=3][FONT=Times New Roman]FROM Samp;[/FONT][/SIZE]
[/SIZE][/FONT]

Any one know how to Replace the #Error signs to the Name(e.g First name & MidName)

Thanks.

 

[khawar]

Attached sample shows how to seperate first and last name from full name