Extract Strings from a Dot Notation Field

[wwalkerbout]

Greetings,

I would like to construct one or more functions that will allow me to extract each string element from a dot notation populated field. An example of the content of such a field (perhaps named [Org_Structure]), would be:

Organization.Division Name.0001 - Region Name.123456 - Area Name.A Rather Long Location Name

In populating the [Org_Structure] field, I'll be able to strip out all special characters between the "." (dot) delimeters except the "-" (hyphen) character.

So what I need to be able to do is then use a function to strip out each of the values between the dot notation delimeters into separate fields.

I have a feeling that this can be done using the "instr" function in conjunction with perhaps the "left" function or some other combination, but I'm not sure what would be the best way to approach this so that I can easily create a separate function for each element to be extracted to a separate field.



Can anyone help me figure out the best way to approach this?

Any assistance would be most appreciated.

Cheers,

Wayne

[June7]

Can use string manipulation functions such as Left, Mid, InStr. I think Split is one you will find very handy for your situation. It can only be used in VBA. Split will parse a string into a one-dimensional array as determined by designated delimiter. In your data the delimiter is dot.

[wwalkerbout]

Hi June7,

Thanks for the prompt response to my post. I am unfortunately limited to MS Access Functions. This is for use in a MS Project Server 2010 formula driven field, which is limited to using Functions (which happen to use the MS Access syntax). So I can't use VB.

I had researched previously and gathered that I can use Functions such as Left, Mid and InStr to accomplish this. But I am not versed in MS Access Function development, so I was hoping someone would be able to provide me with an example of the code needed to target and extract any of the elements in this dot notation based field.

As before, any information on samples of the actual code needed to extract these data elements would be much appreciated.

Cheers,

Wayne

[June7]

I don't know anything about MS Project Server 2010. By 'formula driven field', do you mean a calculation in a query?

If you can't use VBA, parsing that string into the 5 parts delimited by the dots is challenging, especially if the length of each part will vary from record to record. It will mean a treacherous nesting of Left/Right/Mid/InStr/InStrRev functions.

Let's say x is the field with your example string and assume the length of each part is variable.

Part1 is easy
Left(x, InStr(x,".")-1)

Part5 is easy
Mid(x,InStrRev(x,".",Len(x))+1)

Now it gets tricky.

Part2
Left(Mid(x,InStr(x,".")+1),Instr(Mid(x,InStr(x,"." )+1),".")-1)

Part 3
Left(Right(x,InStrRev(x,".",Len(x))),InStr(Right(x ,InStrRev(x,".",Len(x))),".")-1)

Part4
Mid(Left(x,InStrRev(x,".")-1),InStrRev(Left(x,InStrRev(x,".")-1),".")+1)

I developed this code by using the VBA editor Immediate Window to test the constructs.

[wwalkerbout]

Hi June7,

I thought that this would be something that would be difficult to accomplish. You a right about a calculation in a query. Formula driven fields provide a very limited means of taking a field value and using just functions such as Left, Mid, InStr, etc... to manipulate field data.

What you have provided is excellent! This is exactly what would have taken me many hours to figure out. I will test these out withing the Project Server 2010 formula driven field environment later today and let you know if it works.

I really appreciate you taking the time to provide these examples for me. This is a huge help!

Cheers,

Wayne

[June7]

Conceptually, it's not really difficult. Just getting the nesting correct is tricky. I have to build them piecemeal, inside to outside. After I get built, on second look I always wonder 'how in the world does that work!?!'

Good Luck!

[wwalkerbout]

Hi June7,

I just finished testing your example code out within the Project Server 2010 environment and I ran into a problem. I found that the available string functions in Project Server 2010 are very limited. Below is the full list:

Asc( string )
Chr( charcode )
Format( expression, format, firstdayofweek, firstweekofyear )
InStr( start, string1, string2, compare )
LCase( string )
Left( string, length )
Len( string )
LTrim( string )
Mid( string, start, length )
Right( string, length )
RTrim( string )
Space( number )
StrComp( string1, string2, compare )
StrConv( string, conversion, LCID )
String( number, character )
Trim( string )
UCase( string )

So it appears that the 'InStrRev' Function is not available to me. As a result, only the first two examples worked. I guess this will get trickier as the levels get deeper too, right?

When I looked into the details, I found that the organizational structure can be as a much as 8 levels deep. So, for instance, a more realistic example of the full structure would be:

Corporate.Organization.Division Name.0001 - Region Name.Group Name.Market Name.123456 - Area Name.A Rather Long Location Name

I think I could have worked out how to expand thing the code to cater for the additional levels, based on using the InStrRev Function that you had used in your examplee. But, looking at things again without that Function, I am struggling to work out if there is a way to parse all the way down to the 8 levels that I need.

Are you able to suggest a way of doing that, when restricted to just the String Functions that I have listed above?

If it is at all possible (even if the code becomes somewhat long), I'd love to be able to accomplish this. The difference with having this data parsed out and not having it is considerable. I'd have to define many additional fields without this ability.

As before, any additional suggestions you might have would be most welcomed. I really have appreciated your previous assistance.

Cheers,

Wayne

[June7]

I was struggling without the InStrRev function until I remembered about it.

For instance, here is a different structure for Part3.
Left(Mid(Mid(x,InStr(x,".")+1),Instr(Mid(x,InStr(x ,".")+1),".")+1),InStr(Mid(Mid(x,InStr(x,".")+1),I nStr(Mid(x,InStr(x,".")+1),".")+1),".")-1)

On your own for the rest.

Why is data in this format to begin with? Parsing strings is always harder than concatenating.

Can't import to a table with Import wizard?

[wwalkerbout]

Hi June7,

You're right. It is painful that this data is stored in this way. But it is all tied in to a built-in field called the RBS, which incorporates some security elements and therefore cannot be changed. It's painful, but unfortunately that's what I'm stuck with.

However, as long as your code is, provided it works, it would save me a huge amount of effort in the long run.

So I'll analyze what you have done and then work with that to see if I can get it working for the rest of the structure. If I can get it to work, it will really be worth it. I'll post my results.

Again, I really do appreciate the effort you have put in to getting me this far. Perhaps there will be light at the end of the tunnel.

Cheers,

Wayne

[June7]

I just hope you don't run into any string length limits. Part3 is already 144 characters, 255 might be max.

[wwalkerbout]

I probably will. But I'll try my best to be creative. Perhaps walking the string from each end to the middle will help reduce the number of bytes I need in the code.

[June7]

At the risk of sounding pessimistic, that's what the InStrRev was needed for. You don't have that function available. Can only walk from the left.

[wwalkerbout]

Oh. Shows how much I know. I thought I might be able to use the left, right and mid functions in some way to do that. But then I wasn't even sure what they did. Maybe there isn't any light a the end of the tunnel after all.

[June7]

Search Access Help or Google for specifics on string manipulation functions.

[wwalkerbout]

I'll do that. Probably won't get time to work on it until the weekend, but it'll certainly be worth my while if I can get this to work.