Accurately splitting up LastName, FirstName from a Sharepoint Link into a Query

[JohnnyMizchief]

Hell Everyone, I've searched far and wide for a fix for this. I'm not a Dev or programming Guru, but I'm fascinated with the advancements of Access 2010. Anyways, to make a long story short. I'm trying to separate LastName, FirstName into separate fields : FirstName and LastName. The code I have is working for the most part. But I had to create a separate query with the replace statement to get rid of the ","... Looking at the data now, I had people with names like Mac Buren, Tony and I'm only picking up the Mac part in my query. Help?

SELECT Left([Employee Name],InStr([Employee Name]," ")) AS qryLastName, Right([Employee Name],Len([Employee Name])-InStrRev([Employee Name]," ")) AS [First Name], Employees.*
FROM Employees
ORDER BY Left([Employee Name],InStr([Employee Name]," "));

[John_G]

Hi -

Instead of using the blank as the separator, use the comma but don't include it in the data:

SELECT Left([Employee Name],InStr([Employee Name],",")-1) AS qryLastName,
mid([Employee Name],InStr([Employee Name],",")+1) AS [First Name], Employees.*
FROM Employees
ORDER BY Left([Employee Name],InStr([Employee Name],",")-1);

The +1 and -1 ensure that the comma is not included in the query results. Be aware that this solution will cause an error if you get a situation where there is no comma in [Employee Name]

John

[JohnnyMizchief]

Hi -

Instead of using the blank as the separator, use the comma but don't include it in the data:

SELECT Left([Employee Name],InStr([Employee Name],",")-1) AS qryLastName,
mid([Employee Name],InStr([Employee Name],",")+1) AS [First Name], Employees.*
FROM Employees
ORDER BY Left([Employee Name],InStr([Employee Name],",")-1);

The +1 and -1 ensure that the comma is not included in the query results. Be aware that this solution will cause an error if you get a situation where there is no comma in [Employee Name]

John

Thanks John G!!!! That actually worked. But for some reason I'm getting a blank space before the first name? I can deal with that easily enough. But the table is pulling the first and last names with 100% accuracy though. THANK YOU SO MUCH!

[John_G]

Hi -

Glad it worked. The leading blank on the first name is because a blank is the first character following the comma.
Using the TRIM function should fix it:

trim(mid([Employee Name],InStr([Employee Name],",")+1))

John

[JohnnyMizchief]

It fixed it. Now onward to my assett form creation!