The dreaded split

[DevState]

I have a spreadsheet that is imported into a database with a 'Name' field. I need to split it because it is always Last,First,I and I need the First and Last name to be in their own columns....




I've tried using the queries for Trim(Mid([employee],InStr([employee],",")+1,InStrRev([employee] & " "," ")-InStr([employee],","))) but with no luck on getting it to update the current table...

[ranman256]

this code will break up the name and put in columns. (in Excel)
it starts in H, and puts in I & J
adjust this code so it will post the Last ,and First into the correct column:
ActiveCell.Offset(0, 1).Formula = vFirst
ActiveCell.Offset(0, 2).Formula = vLast

Public Sub SplitName()
Dim i As Integer, c As Integer
Dim vWord, vLast, vFirst, vMI, vRem

c = 2
Range("H2").Select
While ActiveCell.Value <> ""
        'If Len(ActiveCell.Value) > 2 And LCase(ActiveCell.Value) <> "patron" Then
            
            vMI = ""
            vWord = Trim(ActiveCell.Text)
            i = InStrRev(vWord, " ")
            vLast = Trim(Mid(vWord, i + 1))
            vRem = vLast
            i = InStr(vWord, " ")
            vFirst = Left(vWord, i - 1)
            
'            If InStr(vRem, " ") > 0 Then
'               i = InStr(vRem, " ")
'               vMI = Left(vRem, i - 1)
'               vLast = Trim(Mid(vRem, i + 1))
'            End If
            
            ActiveCell.Offset(0, 1).Formula = vFirst
            ActiveCell.Offset(0, 2).Formula = vLast
            'ActiveCell.Offset(0, 9).Formula = vMI
        
        'End If
        'c = c + 1
        ActiveCell.Offset(1, 0).Select   'next rows
Wend

End Sub

[DevState]

I need for access and not excel

[isladogs]

Assuming data in the format Last, First with a space after the comma

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

First: Mid(Employee, InStr(Employee, ",") +2)

Use these in an update query

OR use the Split function in a VBA function - it can't be used in a query

[DevState]

I got the general update working if I was to self split the names into their own locations (First and Last columns). I'm unsure how to do this when just updating the current table.

[isladogs]

Import or link to your spreadsheet
Have a separate table for your final result with separate columns.
If records already exist, use an update query where the update to line has the expressions I gave before.
If it's new data, use an append query with those expressions used to append to the last and first name fields.

If it's a mixture of both, you can possibly combine into an 'UPEND' query.
This updates existing records and appends new records in one query.
To do s you use an update query but change it from inner join to outer join

[DevState]

Well I got the update query to break the last and first name, but now it leaves a space and the middle initial in the right of the First name (ex. John I).

Is there any way to just delete the space and the initial?

[isladogs]

Assuming not everybody has a space and initial, you could create a function in a standard module

EDIT: 3 corrections in RED

Function GetFirstName(strText As String)
strText = Mid(strText, InStr(strText, ",") + 2)
If InStr(strtext, " ") > 0 Then
    GetFirstName = Left(strText, InStr(strText, " ") - 1)
Else
    GetFirstName = strText
End If
End Function

Now in your update query, use GetFirstName([Employee]) in the Update To line

[DevState]

Assuming not everybody has a space and initial, you could create a function in a standard module

Function GetFirstName(strText As String)
strText = Mid(strText, InStr(strText, ",") + 2)
If InStr(srtext, " ") > 0 Then
    GetFirstName = Left(strText, InStr(strText, " ") - 1)
Else
    GetFirstName = strText
End If
End Function

Now in your update query, use GetFirstName(Employee) in the Update To line

Says undefined function. I put it in a standard module.

[isladogs]

Apologies: 3 corrections in RED

Function GetFirstName(strText As String)
strText = Mid(strText, InStr(strText, ",") + 2)
If InStr(strtext, " ") > 0 Then
    GetFirstName = Left(strText, InStr(strText, " ") - 1)
Else
    GetFirstName = strText
End If
End Function

Now in your update query, use GetFirstName([Employee]) in the Update To line[/QUOTE]

[DevState]

Same outcome :/

[isladogs]

I tested it this time which is why I made the corrections.
It definitely works for me. See attached

[DevState]

That worked, thanks!

Strange, I had to rename the module back to 'Module1'.

[isladogs]

It doesn't matter what the module is called except the module and function cannot have the same name

[DevState]

Then that explains that...thanks for the help!