CODEWITHSUNDEEP
sqlms-accessselectconcatenation
user2380034
user2380034

Reputation: 73

Split Full Name into First Name and Last Name fields Access

I have this query to retrieve the First Name out of the Full_Name field. 

SELECT Employee_Table.Full_Name, Left([Full_Name],InStr([Full_Name]," ")-1) AS First_Name
FROM Employee_Table;

It works fine,

However, I tried to change the query to get the Last Name into the Last_Name field by changing the query to this one but it did not work. Please Help

SELECT Employee_Table.Full_Name, Right([Full_Name],InStr([Full_Name]," ")+1) AS Last_Name
FROM Employee_Table;

I would like to have only one query that pulls the information and not two separate ones.

Thanks

Regards

Upvotes: 1

Views: 25217

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270401

In your second query, you are pulling from the end of the string, but the length is from the beginning. Oops. The function that you want is MID() rather than RIGHT():

SELECT Employee_Table.Full_Name, Left([Full_Name],InStr([Full_Name]," ")-1) AS First_Name,
       mid([Full_Name],InStr([Full_Name]," ")+1) as Last_Name
FROM Employee_Table;

Upvotes: 2

Related Questions