Characters before searched string

[cleme1q]

Is there a function, similar to mid, except that it will return all characters prior to the searched string instead of after?

Thanks

[pbaldy]

How about Left(), using InStr() to find the position of the string?

[cleme1q]

How about Left(), using InStr() to find the position of the string?

Unfortunately that won't work because there are multiple instances of the character and I am using InstrRev to find the position of the last instance so that I can go backwards from that as content for the next column in the query.

Thanks for the idea.

[pbaldy]

Can you post an example of the string and the desired result?

[cleme1q]

I am using access to link to an Exchange inbox. One of the columns is the content of an email. The amount of text in the content varies with each email. Here is an example of the string but there could be more or less rows of data in the content, always delimted by a |:

ops25 [3084:i2cus02:2] | 2044.0 | SYSLOCAL | launch13-ri2.ri2.dc1.responsys.com | Table Scan | 8% | Blocks | 10/125 | Table Scan: ECUK_CUST.SUP_00000098174: 5081 out of 68584 Blocks done
ops25 [3084:i2cus02:2] | 2730.0 | SYSLOCAL | launch10-ri2.ri2.dc1.responsys.com | Table Scan | 8% | Blocks | 10/125 | Table Scan: ECUK_CUST.SUP_00000098174: 5081 out of 68584 Blocks done
ops25 [3084:i2cus02:2] | 2847.0 | SYSLOCAL | launch14-ri2.ri2.dc1.responsys.com | Table Scan | 8% | Blocks | 10/125 | Table Scan: ECUK_CUST.SUP_00000098174: 5081 out of 68584 Blocks done
ops25 [3084:i2cus02:2] | 2589.0 | SYSLOCAL | launch12-ri2.ri2.dc1.responsys.com | Table Scan | 8% | Blocks | 10/125 | Table Scan: ECUK_CUST.SUP_00000098174: 5081 out of 68584 Blocks done
ops25 [3084:i2cus02:2] | 2390.0 | SYSLOCAL | launch11-ri2.ri2.dc1.responsys.com | Table Scan | 8% | Blocks | 11/138 | Table Scan: ECUK_CUST.SUP_00000098174: 5081 out of 68584 Blocks done
ops25 [3084:i2cus02:2] | 2739.0 | NEWLOOK_CUST | ws02-ri2.ri2.dc1.responsys.com | Table Scan | 37% | Blocks | 175/304 | Table Scan: NEWLOOK_CUST.SUP_00000104743: 52172 out of 142640 Blocks done
ops25 [3084:i2cus02:2] | 2478.0 | INTBUIS_CUST | launch11-ri2.ri2.dc1.responsys.com | Table Scan | 65% | Blocks | 1020/551 | Table Scan: INTBUIS_CUST.PRO_00000097988: 524944 out of 808431 Blocks done
ops28 [3519:i2cus04:4] | 3030.0 | MICHCONS_CUST | launch02-ri2.ri2.dc1.responsys.com | Table Scan | 8% | Blocks | 10/124 | Table Scan: MICHCONS_CUST.SUP_00000001080: 10195 out of 136136 Blocks done
ops7 [1022:cust01:1] | 2426.0 | SFI_CUST | cleit.prod.responsys.com | Table Scan | 83% | Blocks | 2876/617 | Table Scan: SFI_CUST.PRO_00010881853: 1557897 out of 1891855 Blocks done
ops7 [1022:cust01:1] | 2971.0 | PRODAPP_PTRAN | RESPONSYS\CORPDC5700PTRAN | Table Scan | 80% | Blocks | 28098/7350 | Table Scan: SPEEDD_CUST.PRO_00001483931: 2117582 out of 2671454 Blocks done



One column in my query finds the last occurrence of the | and prints out the 30 characters following. The next column in my query should exclude the | that was found, find the next one and print the 30 following characters. I am doing this up to 10 columns.


For example:


SELECT DatePart('ww',[received]) AS WeekoftheYear, OrionInbox2.Received, Mid([contents],InStrRev([OrionInbox2]![Contents],"|"),30) AS Account,
Mid(Mid([contents],InStrRev([OrionInbox2]![Contents],"|")),InStrRev([Contents],"|",InStrRev([Contents],"|")-180),30) AS Account2,


For the Account2 portion of the query I am trying to exclude the data from Account that was found as the content of Mid but because Mid only displays forward characters, it does not work correctly.

Ideally, Account would return '| Table Scan: SPEEDD_CUST.PRO' and Account2 would return ' | Table Scan: SFI_CUST.PRO_0'

I know this seems convoluted.. thanks for the help.