Parsing Queries / Function

[jaaret]

I'm using Access to analyze a MS Project 2007 project plan. I'm writing queries that I can adapt into functions to parse the project Work Breaksdown Structure (WBS) codes.

To link a Level 4 task with its parent Level 3 task I have to parse the third number in the WBS code. So far I have been able to get this to work with the complex, nested function, below:

ParWBS: Left(Right(Right([wbs],Len([wbs])-2),Len(Right([wbs],Len([wbs])-2))-InStr(1,Right([wbs],Len([wbs])-2),".")),InStr(1,Right(Right([wbs],Len([wbs])-2),Len(Right([wbs],Len([wbs])-2))-InStr(1,Right([wbs],Len([wbs])-2),".")),".")-1)

This has worked so far with Level 4 WBS codes of different lengths:
1.6.10.1 results in 10
1.6.9.2 results in 9
1.12.9.3 results in 9

Is there a simpler, more straightforward way to approach this?

If you would like a little more background, read on.
Project has a Unique_ID field which acts as a primary key. However, the Unique_ID field will re-index (not sure yet what triggers re-indexing) so using it as a primary key in Access is useless. I'm creating a primary key in Access and parsing the WBS code is my method for linking the sub-tasks with their parent tasks.

Thanks for your input!

Jaaret

 

[Remou]

You could use split:

WBS="1.6.10.1"
a=Split(WBS,".")

[tt] a(0) returns 1
a(2) returns 10

Split(WBS,".")(2) returns 10[/tt]

 

[MajP]

This should be done using a self referencing query. Then I would show this in a treeview

You can return the ParentID of each node

Public Function getParentID(WBS As String) As Variant
  If Not Trim(WBS & " ") = "" Then
     If InStrRev(WBS, ".") = 0 Then
       getParentID = Null
     Else
       getParentID = Left(WBS, InStrRev(WBS, ".") - 1)
     End If
  End If
End Function

and you can get the level of each node

Public Function getLevel(WBS As Variant) As Integer
  If Not Trim(WBS & " ") = "" Then
    getLevel = UBound(Split(WBS, ".")) + 1
  End If
End Function

Using these functions in a query. Something like

SELECT tblWBS.wbs, getParentID([wbs]) AS ParentID, getLevel([wbs]) AS [Level]
FROM tblWBS;

output

wbs ParentID Level
1 1
1.2 1 2
1.2.1 1.2 3
1.2.2 1.2 3
1.2.3 1.2 3
1.3 1 2
2 1


With this structure even without the Level you can populate a treeview to show the WBS. (Disregard the formatting, level 1.2/1.3 would not align)

 

[MajP]

This may be of interest

http://www.4shared.com/file/77263021/b941cb3/Generic_TreeView.html

 

[MajP]

Using the above query you could make queries for each level and then you could use subdatasheets. This would produce something like. Obviously the tree view looks better.

http://i39.tinypic.com/x0tz.jpg