Query to Extract Portion of Text between 2 defined parameters

[Cydney]

I would like a query function that will extract out only the text between
<Note> and </Note> in a variable lengthed text field. Sometimes the
parameters won't exist, and in that case, I would want to leave the text only
that exists in that field.

Here's an example:
<ES><Date=`3/19/2008 2:37:44 PM` Note=`Hour change from 0 to 1.5 hours on
Mon`>0</Explain><Note>made fig changes to file</Note><Explain Date=`3/19/2008
2:37:44 PM` Note=`Hour change from 0 to 1.4 hours on
Mon`>0</Explain><Date=`3/19/2>

I would only want to have the text "made fig changes to file" remain.

Can you help me create this function?
--
Thank you, cs
~~~~~~~~~~~~~~~~~~~~~~~~
"What lies behind us and what lies before us are tiny matters compared to
what lies within us."
~ Ralph Waldo Emerson

 

[John Spencer]

This expression gets everything after the <Note>
Mid(TheField,Instr(1,TheField,"<Note>")-6)

This expression where x = Above gets everything up to the "</Note>
Left(x,Instr(1,x,"</Note>")-1)

Combined that is the following
Left(Mid(TheField,Instr(1,TheField,"<Note>")-6),
Instr(1,Mid(TheField,Instr(1,TheField,"<Note>")-6),"</Note>")-1)

Now testing the note to see if both delimiters are in it and if they are
return the calculated string, otherwise return the entire string

IIF(TheField Like "*<Note>*<[Note>*",
Left(Mid(TheField,Instr(1,TheField,"<Note>")-6)
,Instr(1,Mid(TheField,Instr(1,TheField,"<Note>")-6) ,"</Note>")-1)
,TheField)

"V50604"
'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================