Parsing question--inStr 1

[des0929]

I have a description field --DESCR--in Access. The field is the result of the concatenation of 3 fields in another application. I would like to split this DESCR field. Here is an example:
DESCR
----------------------------------
SCENARIO: Regional Reimbursable Commodity SCRIPT: REM1000

The values of "SCENARIO:" and "SCRIPT:" are constant in every record. How can I parse out the "Regional Reimbursable Commodity" and the "REM1000" text into two new fields from my query?
MS Access 97

 

[mpastore]

' returns "Regional Reimbursable Commodity"
s1 = Trim(Mid(s, InStr(s, ":&quot + 1, InStr(s, "SCRIPT:&quot - (InStr(s, ":&quot + 1)))

' returns REM1000
s2 = Trim(Mid(s, InStr(s, "SCRIPT:&quot + Len("SCRIPT:&quot))


Mike Pastore

Hats off to (Roy) Harper

 

[des0929]

Thanks for your help Mike and your quick reply.Your suggestion worked great!...if you have a moment, can you take a look at one more related question. The only difference is that I would need to parse out three values from this one string. How would I write the expression to bring back the following:

DESCR
----------------------------------
SCENARIO: Regional Reimbursable Commodity SCRIPT: REM1000 APPLICATION: Test Director

The values of "SCENARIO:" and "SCRIPT:" and "APPLICATION:" are constant in every record. How can I parse out the "Regional Reimbursable Commodity", "REM1000" and "Test Director" text into three new fields from my query?
MS Access 97

 

[mpastore]

' returns "Regional Reimbursable Commodity"
Trim(Mid(s, InStr(s, ":&quot + 1, InStr(s, "SCRIPT:&quot - (InStr(s, ":&quot + 1)))

' returns REM1000
Trim(Mid(s, InStr(s, "SCRIPT:&quot + Len("SCRIPT:&quot, InStr(s, "APPLICATION:&quot - (InStr(s, "SCRIPT:&quot + Len("SCRIPT:&quot)))

' returns Test Director
Trim(Mid(s, InStr(s, "APPLICATION:&quot + Len("APPLICATION:&quot))


Mike Pastore

Hats off to (Roy) Harper

 

[des0929]

One more question:

How about parsing this one:
Field value:
View Bid History INPUT:In the Process Bid invitations screen, click on the History hyperlink.

INPUT: value is always constant.

I need the "View Bid History" string.

Thanks again!

 

[mpastore]

Left(s, InStr(s, "INPUT:&quot - 1)

You really should read up on the instr, left, mid commands, once you get the hang of them they are really quite simple.

Mike Pastore

Hats off to (Roy) Harper