parsing - Microsoft Access: Complex string search to update field in another table -

i have table linked access return results of emails folder. of emails being returned answering same questions. need parse email body text table , update several fields of table data. problem linked table brings text in super messy. though have email being returned nicely formatted in table, comes access hot mess full of spacing. want open recordset based on linked table (linktable), , parse linktable.body field somehow can update table clean data. data coming linktable looks this:

permit? (note: if yes, provide specific permit type in additional requirements section)

no 

phytosanitary certificate? (note: if recommended, input no , complete additional requirements section)

yes 

additional requirements: if not applicable, indicate na or leave blank (type of permit required, container labeling, other agency documents, other)

double containment, labeling or declaration must provide following information: -the kind, variety, , origin of each lot of seed -the designation “hybrid” when lot contains hybrid seed -if seed treated, name of substance or p 

the answer of first 2 should either yes or no, figured set code case statements , based on match should place yes or no in corresponding field in real table (not sure how deal spaces here), third 1 have number of responses, last question after "(type of permit required, container labeling, other agency documents, other)" taken , placed in other table. have ideas how set up? @ bit of loss, how deal of spaces , how grab of text after additional requirements paragraph. thank in advance!

my select statement body text looks this:

set rst1 = db.openrecordset("select subject, contents linktable subject '*1710'") 

there multiple ways this, 1 using instr() , len() find beginning , end of fixed questions, mid() extract answers.

but think using split() easier. it's best explained commented code.

public sub theparsing()      ' string constant expect never come in contents, used separator split()     const strseparator = "##||##"      dim rst1  recordset     dim s string     dim aranswers variant     dim long      s = nz(rst1!contents, "")     ' replace constant parts (questions) separator     s = replace(s, "permit? (note: if yes, provide specific permit type in additional requirements section)", strseparator)     ' etc. other questions      ' split remaining string 0-based array answers     aranswers = split(s, strseparator)      ' aranswers(0) contains before first question (probably ""), ignore that.     ' check there 3 answers     if ubound(aranswers) <> 3         ' houston, have problem         stop     else         = 1 3             ' extract each answer             s = aranswers(i)             ' remove whitespace: crlf, tab             s = replace(s, vbcrlf, "")             s = replace(s, vbtab, "")             ' trim remaining string             s = trim(s)              ' have cleaned string , can use             select case                 case 1: strpermit = s                 case 2: strcertificate = s                 case 3: strrequirements = s             end select         next     end if      rst1.movenext     ' etc  end sub 

this fail if constant parts (the questions) have been altered. other straightforward methods.