Parsing Display Data out of a Hyperlink Data Field

[SamIam]

I need some help parsing out some information. I have an existing hyperlink data field that I am trying to parse out the display information into up to 4 separate parts. I am new to Access so I have been trying to do everything as an expression in the design table and staying away from using VB. I have experience in VB, but not in sure how to make it work in Access. I figured out Part 1 and Part 2, but am struggling with Parts 3 and 4 when the variable lengths come into play. Here is what I am working with:

Data in field “part_number”
AAAA 12.234-1#XXXXXXXXXXX
AAAA 12.234-11#XXXX
AAAA 12.234-11 AAA I#XXX
AAAA 12.234-11 AAA II#XXXXXX
12.234-100#XXXXX
BB 12.423-90#XXXXXXXXXXXXX
1234.842-2 AAA III#XXXX

Part 1 – This part will vary in length. I want to include everything up to first “.”

This is what I get using the following expression:

Part1: Left([part_number],InStr([part_number],".")-1)

AAAA 12
AAAA 12
AAAA 12
AAAA 12
12
BB 12
1234

Part 2 – This part will always have 3 numbers. I want it to include everything between the “.” and the “-“.

This is what I get using the following expression:

Part2: Mid([part_number],InStr([part_number],".")+1,3)

234
234
234
234
234
423
842



Part 3 – This part will vary in length. I want to include everything from the “-“ to the first “ “ after the “-“ if there is one, or to the “#” if there is not a “ “. I have playing with another Mid function here, but I haven’t been able to figure how to account for the variable length between the “-“ and the “ “ or “#” depending on what is there.

This is what I'm looking to get for this part:
1
11
11
11
100
90
2

Part 4 – This part will vary in length. I want to include everything from second “ “ after the “-“ to the “#” if there is something there. Same thing here. I’m expecting to use another Mid function, but the variable length throws me off.

This is what I'm looking to get for this part:
0
0
AAA I
AAA II
0
0
AAA III

The data after the “#” is the rest of the hyperlink data that varies in length, and is extraneous data that I don’t need.

Any help on parts 3 and 4 would be greatly appreciated.

Thanks in advance for your time and patience.

[June7]

Part 3: Trim(Val(Mid([part_number],InStr([part_number],"-")+1)))


Part 4: really tricky because of the inconsistency and several spaces

Let x represent your field

y=Left(x,InStr(x,"#")-1)

Trim(Mid(y,InStr(y,"-")+1 + Len(Trim(Val(Mid(y,InStr(y,"-")+1))))))

All in one

Trim(Mid(Left(x,InStr(x,"#")-1),InStr(Left(x,InStr(x,"#")-1),"-")+1 + Len(Trim(Val(Mid(Left(x,InStr(x,"#")-1),InStr(Left(x,InStr(x,"#")-1),"-")+1))))))

or this one

IIf(InStr(x," A")>0, Left(Mid(x,InStr(x," A")+1),InStr(Mid(x,InStr(x," A")+1),"#")-1),0)

[SamIam]

June-

You are awesome! I was definitely making Part 3 harder than it had to be. I did end up switching the Val and Trim functions in Part 3 so I could sort on the output from the expression. There is no way I would have ever figured out Part 4. I went with the IIF expression, as again, it allowed me to complete a sort on the output from the expression.

Thanks again for all of you help. Happy Holidays!!!

[June7]

Then don't really need the Trim function at all.

I think Part 4 sort results would be the same with either version.