Parsing a String 1

[Elysynn]

Hello - I'm attempting to extract team information from a hierarchy string. The format is as follows /ParentGroup/SiteLocation/Manager/Team/

Each of the hierarchy members is of variable length except for the parent group. I am currently attempting to use this expression in my query:

Team: Right([hierarchy_names],Len([hierarchy_names])-InStr(1,[hierarchy_names],"/"))

The result I am getting - is the entire string. The result I am trying to achieve is: Team

I've been struggling with this for a couple of hours - any assistance is greatly appreciated.

TIA,
Elysynn

 

[PHV]

In a standard code module create the following function:

Public Function getElem(str, delim As String, N As Integer)
If IsNull(str) Then Exit Function
Dim myArr
myArr = Split(str, delim)
If N >= 1 And N <= (1 + UBound(myArr)) Then
  getElem = myArr(N - 1)
End If
End Function

And now, in the query grid:
ParentGroup: getElem([hierarchy_names], '/', 2)
SiteLocation: getElem([hierarchy_names], '/', 3)
Manager: getElem([hierarchy_names], '/', 4)
Team: getElem([hierarchy_names], '/', 5)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Elysynn]

PH - This is beautiful. Thank you so much for your help! Have a star!

-Elysynn