×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

I need to un-concatenate something complex for an Access query.

I need to un-concatenate something complex for an Access query.

I need to un-concatenate something complex for an Access query.

(OP)
I need to un-concatenate for a query in Access. I need to separate a first name, last name and badge number. I don't need "Driver" in this anywhere. Just the name and badge number. Here are some examples of my information:

Driver: WALKER III, IRVIN W. (422103)
Driver: CASTRO COREA, JACKSON (422227)
Driver: DAVIS-BOSTON, LES S. (421550)

I have done simple separations but this is too complex for my limited knowledge of Access. Any assistance would be greatly appreciated.

RE: I need to un-concatenate something complex for an Access query.

I would create two custom functions:

Function TheName(EntryString As String) As String
    TheName=Trim(Split(Replace(EntryString, "Driver",""),",")(0))
End Function 

and

Function BadgeNumber(EntryString As String) As String
    BadgeNumber=Replace(Split(EntryString,"(")(1),")","")
End Function 

combo

RE: I need to un-concatenate something complex for an Access query.

(OP)
I apologize, I am unsure of where to put these functions? In Design View, will these go into the "SQL" section? I typically have input the data into the "Field" section of the actual query when it came to separating names in the past. But nothing this complex.

RE: I need to un-concatenate something complex for an Access query.

Database tools, Visual Basic. Add module (regular) and paste the code. Next use them in this database queries as regular functions. They can be found in expression builder as functions in your database.
Formulas to paste after correction (to get full name):

Function TheName(EntryString As String) As String
    TheName = Trim(Split(Replace(EntryString, "Driver:", ""), "(")(0))
End Function

Function BadgeNumber(EntryString As String) As String
    BadgeNumber = Replace(Split(EntryString, "(")(1), ")", "")
End Function 

Access VBA forum: forum705: Microsoft: Access Modules (VBA Coding)

combo

RE: I need to un-concatenate something complex for an Access query.

"separate a first name, last name and badge number" - so combo's TheName Function could be modified this way:

Function TheName(EntryString As String, aPart As Integer) As String
    TheName = Trim(Split(Replace(EntryString, "Driver:", ""), "(")(0))
    TheName = Split(TheName, ", ")(aPart)
End Function
 
So you use it like this:
If you want to get First Name: TheName(yourfield, 0) As FirstName
To get the Last Name: TheName(yourfield, 1) As LastName

I hope it works, but all the glory goes to combo thumbsup2


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close