• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1649
  • Last Modified:

How can I parse a name into separate fields within an Access Query

I have a field [Employee] that is formatted as follows [Lastname, Firstname, Middle], i.e.:

    Doe, John M

I need to separate the First and Last Names into their own individual fields within a query in MS Access (I don't need the middle initial at all, just the First and Last Names), i.e.:

   LastEmpName
   FirstEmpName

I have the following string for it to pull the Last Name out of the Employee field (and this works fine):

  LastEmpName: Left([tblEmployee_Audits].[Employee],InStr([tblEmployee_Audits].[Employee],",")-1)


I have the following string for it to pull the First Name out of the Employee field (this is not working correctly.  It is including the Middle Initial along with the First Name). See string below:

FirstEmpName: Trim(Mid([tblEmployee_Audits].[Employee],InStrRev([tblEmployee_Audits].[Employee],", ")+1))

Also, some names have a Middle Initial and some don't have one.

How do I write the string in the query for the FirstEmpName so that it doesn't include the Middle Initial?

Thanks,

gdunn59
1
gdunn59
Asked:
gdunn59
1 Solution
 
loki0609Commented:
0
 
Rey Obrero (Capricorn1)Commented:
try this

FirstEmpName: Trim(Mid([employee],InStr([employee],",")+1,InStrRev([employee] & " "," ")-InStr([employee],",")))
0
 
Dale FyeCommented:
The easy way is:

split(myName, " ")(1)

Unfortunately, you cannot use that syntax in a query, so I use a function:

Public Function fnParse(ParseWhat As Variant, Delimiter As String, _
                        Position As Integer) As Variant

    Dim myArray() As String

    If IsNull(ParseWhat) Then
        fnParse = Null
    Else
        myArray() = Split(ParseWhat, Delimiter)
        If Position = 0 Or Position > UBound(myArray) + 1 Then
            fnParse = Null
        Else
            fnParse = myArray(Position - 1)
        End If
    End If
    
End Function

Open in new window

In your query, you would use:

FirstEmpName = fnParse([employee], " ", 2)
0
 
gdunn59Author Commented:
Worked like a charm!

Thanks,
gdunn59
0
 
Dale FyeCommented:
glad to help
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now