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:
Code: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:
Code: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 havent 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. Im 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 dont need.
Any help on parts 3 and 4 would be greatly appreciated.
Thanks in advance for your time and patience.