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

[Pinnacle22]

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.

 

[combo]

I would create two custom functions:

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

and

[pre]Function BadgeNumber(EntryString As String) As String
BadgeNumber=Replace(Split(EntryString,"(")(1),")","")
End Function[/pre]


combo

 

[Pinnacle22]

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.

 

[combo]

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

[pre]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[/pre]

Access VBA forum: forum705

combo

 

[Andrzejek]

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

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

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


---- Andy

There is a great need for a sarcasm font.