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

 

[MajP]

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?

 

[PHV]

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

 

[bdmoran]

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.

 

[dhookom]

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

 

[MajP]

PHV, I did not think about Sandbox. Thanks.

 

[bdmoran]

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

 

[dhookom]

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

 

[bdmoran]

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.

 

[dhookom]

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

 

[bdmoran]

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

 

[dhookom]

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

 

[bdmoran]

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!