CODEWITHSUNDEEP
ms-accessms-access-2016
Nick T
Nick T

Reputation: 101

MS Access: Separating out first and last name into separate fields

I have a field with thousands of records. In those records are a bunch of names.

The problem is both the first and last name are listed together (rather than in two different fields). How would I go about separating them out. The format right now for the names is John Smith

FirstName: Left([PlayerName],InStr([PlayerName]," ")-1)

I tried this but it did not work. It just returned #Func! for each record in my FirstName query field

Any help would be appreciated. Thank you.

Upvotes: 1

Views: 6053

Answers (1)

Chris
Chris

Reputation: 254

I'm thinking there is a problem with the data in your original table, for example: there could be a space before "John" in "John Smith" (" John Smith").

Good way to check is using the replace function.

SELECT Replace([PlayerName]," ","-") as [TestName]
FROM YourTableName;

If there is a dash in front of your first name. That is the problem.

You can also try checking the function you are trying to use. instr([Start],[String1],[String2]) might require to have a 1 placed in the "[Start]" portion of the function.

Basically your new function would look as follows:

FirstName: Left([PlayerName],instr(1,[PlayerName]," ")-1)

Let me know if either of these solutions do not work and ill do more digging.

Upvotes: 1

Related Questions