Splitting Data

[bdmoran]

Hello - There is a column in one of my tables in access that has an employee name and employee ID formatted like this:

Smith, Mike [567891012]
Pike, Tim [9898]

I am looking to get the employee number in a seperate column. I was thinking to create a query with the

function, but every employee ID is a different number.

any ideas? Thanks!​

 

[dhookom]

Try an expression like:

Replace(Mid("Smith, Mike [567891012]",Instr("Smith, Mike [567891012]","[")+1),"]","")

Duane
Hook'D on Access
MS Access MVP

 

[MazeWorX]

with vba you could loop the record set

Function SplitName()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim Employeenumber As Variant

Set db = CurrentDb()
Set rs = db.OpenRecordset("Table1")

Do Until rs.EOF
With rs
Employeenumber = Split(rs!Employee, " ")
Debug.Print Employeenumber(2)
.MoveNext
End With
Loop

End Function

HTH << MaZeWorX >> "I have not failed I have only found ten thousand ways that don't work" <<Edison>>

 

[Andrzejek]

Split by the space would be the way to go as MaZeWorX suggested - with Replace [ and ] with "" - , but I would be careful to go after the 2nd element of the array, because you may have something like
[tt]
Smith, Mike [567891012]
Brows, Bob [red]L[/red] [123456]
Pike, Tim [9898]
[/tt]
SO I would go after [tt]UBound(Employeenumber)[/tt] for ID

Have fun.

---- Andy

 

[lameid]

My first thought was to go for Split on the left bracket, take the second element and then deal remove the ending square bracket....

Duane's soultion does all that with an expression that can be used in an update query without writing a function. The only thing that tripped me up is not realizing the Mid function's third parameter is optional as Duane used it. So I learned something I wasn't trying to today.

I definitely think whatever solution should be left bracket based as this seems like it would be the one consistent piece.

The thing that may not be obvious is you can replace the the repeated literal text in Duane's example with a field name or variable depending on how you are using it.

 

[dhookom]

lameid,
Don't get too hooked on leaving out the third parameter since I don't think this is supported in Excel and possibly other environments. It is handy once in a while in Access.

I thought about using some more Left() and Len() to remove the trailing "]" but Replace() was shorter and I'm lazy.

Thanks for the comment about the literal text. bdmoran didn't provide a field name so I get a little stubborn and leave some things for the OP to figure out ;-)

I applaud the effort to separate out the number and suggest dbmoran not stop there but also split the name into FirstName and LastName.

Duane
Hook'D on Access
MS Access MVP

 

[lameid]

I'm not hooked on the three parameters, I just had a mental block because I hadn't noticed the third was optional... Picking that up is actually quite useful as I have done unnecessarily complex things before.

And I agree I like to point rather than solve a lot of times too.

Substituting something for the literal text I thought was a bit too counterintuitive to pick up contextually.

 

[bdmoran]

Hi there - Sorry for the late response. I tried using this function but the error message "The function you entered contains the wrong number of argument" came up.

My field looks like this:

Employee

SMITH, TIM [000124502]
SMITH, TIM [000124]
SMITH, TIM [0001]

I declared the function you provided me with in access then tried using this in my query: SplitName([Employee])but I recieved the error mentioned above. I am new to VBA so I apoligize in advance..

 

[dhookom]

Did you try:

Replace(Mid([Employee],Instr([Employee],"[")+1),"]","")

Duane
Hook'D on Access
MS Access MVP

 

[bdmoran]

dhookom - I am having trouble declaring the function. The text shows up red and I recieve the message: "Compile Error: Expected"

 

[dhookom]

I didn't ever suggest you create a function since Replace() Mid() and Instr() are all existing functions in Access.

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

This is a little more bullet proof. Works on all examples proposed to include null fields.
null

Smith, Mike [567891012]
Brows, Bob L [123456]
Smith [123]
Smith, Mike L Jr. [456]
Smith, Mike [567891012]
Brows, Bob L [123456]

Public Function GetEmployeeNumber(fldData As Variant) As Variant
  Dim aData() As String
  Dim strData As String
  Dim i As Integer
  If Not IsNull(fldData) Then
    aData = Split(fldData, " ")
    For i = 0 To UBound(aData)
      strData = aData(i)
      If Left(strData, 1) = "[" Then
        strData = Replace(strData, "[", "")
        strData = Replace(strData, "]", "")
        GetEmployeeNumber = strData
       End If
     Next i
  End If
End Function

to use
select getEmployeeNumber([fieldName]) as Employeenumber ....

 

[bdmoran]

Dhookom - When I tried your code in my query I first recieved an error that said "Replace" wasn't defined. So that's why I assumed to create a function...


MajP this worked out great. Thanks! Is it possible to seperate these fields when appending from excel to access?

 

[dhookom]

If Replace() didn't work in your query then I expect you might be working in an older and/or unpatched version of Access.

Duane
Hook'D on Access
MS Access MVP

 

[bdmoran]

MajP - Is it possible to take out the employee ID from the employee column entirely? As of right now I have it Employee ID as a seperate column, but the ID still remains in the Employee name column.

 

[MajP]

So assume you have a field EmployeeName and EmployeeID
EmployeeName EmployeeID
Smith, Mike [567891012] 567891012

then just do an update query
UPDATE EmployeeName SET EmployeeName = Replace([EmployeeName],"[" &[EmployeeID]&"]","")

So you are just replacing [567891012] in the employeename field with and empty string

 

[bdmoran]

I forgot to mention I have an older version of Access so Replace() doesn't work in my query.

This is taking it back a few steps but im just curious if its possible for me to seperate these two columns when doing a transferspreadsheet?

So for instance Employee Column consists of :

Smith, Donald [98899]
Lawlor, Deb [333wwww00]

When I use transferspreadsheet, can I make it so the employee name appends to an Employee field in my Timesheet table and employee ID appends to an employee ID field in my Timesheet Table?

 

[dhookom]

If Replace() doesn't work in your "older" but unspecified version of Access, then you will need to create a function that does work which would be the one suggested by MajP. Then use this expression in your query:

GetEmployeeNumber([Employee])

Duane
Hook'D on Access
MS Access MVP

 

[MajP]

The original function also used the replace function, so if that worked I am skeptical that it does not work in the query. But...

Public Function RemoveEmployeeNumber(fldData As Variant) As Variant
  Dim aData() As String
  Dim strData As String
  Dim i As Integer
  If Not IsNull(fldData) Then
    aData = Split(fldData, " ")
    For i = 0 To UBound(aData)
      strData = aData(i)
      If Not Left(strData, 1) = "[" Then
        RemoveEmployeeNumber = RemoveEmployeeNumber & strData
       End If
     Next i
  End If
End Function

 

[dhookom]

MajP,
There have been issues with some functions that work in VBA but not in queries. Replace() was one of these functions I believe in Access 2000. I recall creating a "wrapper" function that simply called the Replace() function inside it. Replace() didn't work in the query but fReplace() did work.

Function fReplace(varText As Variant, varFind As Variant, varReplacement As Variant) As Variant
    fReplace = Replace(varText & "", varFind & "", varReplacement & "")
End Function

Duane
Hook'D on Access
MS Access MVP