MS Access query to parse string into 2 records

I have a text field called "Title" with records like this:

Selexipag for pulmonary arterial hypertension
Rilotumumab (AMG-102) for Gastric tumors with high MET expression
Vicrostatin for Cancer


I would like to parse these records in 2 fields: Drug and Indication, so that the query return two columns:

Drug                                   Indication
Selexipag                            pulmonary arterial hypertension
Rilotumumab (AMG-102)   Gastric tumors with high MET expression
Vicrostatin                          Cancer
tomfolinsbeeAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Dev-Soln LLCCommented:
if the term "for" will separate the drug from the indication, then you could use something like:

SELECT Left([yourFieldname], instr([yourFieldName], "for") -1) as Drug,
             Mid([yourFieldname], instr([yourFieldName], "for") + 4) as Indication
FROM yourtable
PatHartmanCommented:
Lecture first - fields should be atomic.  They should never contain multiple attributes.

If the field is reliably constructed so that the drug and indication are ALWAYS separated by the word " for " surrounded by spaces, you can use that string as the delimiter.

SELECT Table1.Title, Left([Title],InStr([Title]," for ")-1) AS Drug, Mid([Title],InStr([texTitlet1]," for ")+5) AS indication
FROM Table1;

If there is any deviation in the formatting of the string, all bets are off.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
tomfolinsbeeAuthor Commented:
Worked perfectly first time. Thank you!
Dale FyeOwner, Dev-Soln LLCCommented:
Guess I should have used [Title] instead of [YourFieldName]!
tomfolinsbeeAuthor Commented:
Dale, very sorry. I didn't see your post, I should have scrolled up. Small screen. Again apologies for not splitting the points. My mistake...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.