CODEWITHSUNDEEP
sqlms-accessms-access-2010
SamR
SamR

Reputation: 543

Split Last Name First Name and Middle Name in access Query

I have Full name Field that I would like to split and remove the middle name from the name.

Names are like:

Smith,James D - > Result be : Smith, James

Doe,John Snow -> Result be: Doe, John

Here is what I did but not sure what I am missing to remove the Middle name

FName: Mid([Employee] & "",InStr(1,[Employee] & " ",",")+1)
Lname: Left([Employee] & "",InStr(1,[Employee] & "",",")+(InStr(1,[Employee] & "",",")>0))

Smith,James D - > I get -> FName: James D

Doe,John Snow -> I get -> FName: John Snow

Upvotes: 1

Views: 2696

Answers (3)

June7
June7

Reputation: 21389

Consistency of structure is critical in string manipulation. Assuming there will always be 3 and only 3 parts to each name and 3rd part is middle name/initial and there is no space following comma, consider:

LastFirst: Left([Employee], InStrRev([Employee]," ")-1

Last: Left([Employee], InStr([Employee],",")-1)

First: Mid(Left([Employee], InstrRev([Employee]," ")-1), Instr([Employee],",")+1)

Middle: Mid([Employee], InStrRev([Employee], " ")+1)

If any of the assumptions do not hold, then build a VBA custom function. Call that function from query or textbox. Other answers are showing excellent methods of parsing name string with VBA function.

The more variation from assumptions, the more complicated the code. Enough variation and this could become virtually impossible to automate.

Upvotes: 3

Krish
Krish

Reputation: 5917

Haven't tested but wouldn't this work for you? Within a vba public function.

'First word being first name
FName= split([employee], ",")(0)

'Second word being the last name. This will throw `indexOutOfRange` error if employee name is only one word. Use `on error resume next` to silently ignore?
LName = split(replace([employee], ",", " "))(1)

You cannot use split directly in ms access sql but that doesn't stop you to create a custom split function.

Public function FnSplit(text as variant, index as integer, optional d as string = " ") as string
    If nz(len(text),0) = 0 then exit function
    On error resume next
    FnSplit = split(text,d)(index)
End function

And use in your sql

Select
   FnSplit([employee],0) as fName,
   FnSplit(replace([employee], ",", " "),1) as lName 
From
   Your table

Obviously these are just another way of doing the same work as previous answers

Upvotes: 1

Vlado
Vlado

Reputation: 888

The following simple function removes Middle name and returns only Last, First even when there is no Middle name or multiple Middle Names in FullName:

Public Function FixName(fullName) As String
    Dim last() As String
    Dim first() As String

    last = Split(fullName, ",")
    FixName = last(0)

    first = Split(last(1), " ")
    FixName = FixName & ", " & first(0)
End Function

Upvotes: 2

Related Questions