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[Right] function, but every employee ID is a different number.

any ideas? Thanks!

Try an expression like:

Duane
Hook'D on Access
MS Access MVP

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>>

   
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

Smith, Mike [567891012]
Brows, Bob L [123456]
Pike, Tim [9898]

SO I would go after UBound(Employeenumber) for ID

Have fun.

---- Andy

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.

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

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.

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..


 

Did you try:
 

Duane
Hook'D on Access
MS Access MVP

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

 

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

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]


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

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?

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

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.    

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

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?

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:

Duane
Hook'D on Access
MS Access MVP

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

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.


 

Duane
Hook'D on Access
MS Access MVP

Thanks. I never heard of that, but A2k seemed like it was short lived. I think I went straight from 97 to 2003. I have seen where all the string functions stop working due to a reference issue to the vba library.  Often noticed first in a query, but systemic to the project.
  One thing I have seen is performance issues between the inline function or the same function wrapped.  I have never heard this discussed in query performance. I use to try to always write the UDF because you could error check and make the SQL a lot cleaner.  Then I noticed the exact same function written inline was orders of magnitude faster than the same function wrapped in a UDF. Ever seen this?   

I never heard of that
Do a google search for jetsql sandbox

Maj P - this works great, thanks!  Only question I have is how come when I call for the "Employee Name" Field on my report, it prompts me to enter in a value.  Where as before when I didn't use your function it just synced the data.

dbmoran,
If you are prompted for a value it typically means you have something in your report bound to a name that isn't in your report's record source. Check your record source and check all of your control sources and sorting/grouping.

Duane
Hook'D on Access
MS Access MVP

PHV, I did not think about Sandbox. Thanks.

I have other fields in my report that are working perfectly.  Would it have something to do with the function??

Provide your SQL statement of your report's record source as well as the prompted names. We can't fix what we can't see. Do you have a field/column [employee name]?

Duane
Hook'D on Access
MS Access MVP

SELECT RemoveEmployeeNumber([Employee]) AS [Employee Name], Timesheet.Date, Timesheet.[IN Time], Timesheet.[OUT Time], Format([IN time]-[OUT time],"short time") AS Actual, IIf([Reg hrs] Between 3 And 4,4,Roundtime([Actual])) AS Regular
FROM Timesheet
WHERE (((Timesheet.Date) Between [Start Date] And [End Date]));


On my report I am looking to pull [Employee Name] but it's asking me for a parameter.  I have the [Actual] column on my report and it works just fine.

Find the control that is bound to [Employee Name] and re-select it from the dropdown in the control source.

Duane
Hook'D on Access
MS Access MVP

Still no luck.  I just deleted the field entirely and it is still prompting me for a parameter.  This didn't happen until I added the function RemoveEmployeeNumber() to my query

Did you check your sorting and grouping as I suggested previously?

Is the prompt asking for Employee Name or Employee or what?

Duane
Hook'D on Access
MS Access MVP

It WAS a grouping and sorting error.  What I did was re-select Employee Name in the dropdown and it worked magic.  

This website has been very helpful to me.  Thanks everyone for your help and support!