Instr Question...

[116]

I have a memo field which contains at the bottom, specific text following
'<DO NOT EDIT>'. I would like to peel out everything after the '<DO NOT
EDIT>' for reporting. Having trouble trying to format. Any help would be
greatly appreciaited.

Thanks
David

 

[Duane Hookom]

What do you mean by "peel out"? Do you mean just remove everything after or
return everything after?
=Mid([MemoField], Instr([MemoField],"<DO NOT EDIT>")+14)

If you want everything before, use the Left() function.

 

[116]

Thank you. And yes...I meant extract when I stated 'peel out'. This was
working fine. I was just thrown a curve ball. The <DO NOT EDIT> has just
become random. If this string is anywhere with in my text, can I extract
that as well? Meaning, If I have <DO NOT EDIT> at the beginning and the end
of the string I am looking for, can I extract that text? If so, I can Change
the statement at the beginning and end to extract many different strings.
The database we use has limited fields for cutome data. This would gives me
more ways to store and extract what is needed.

What do you mean by "peel out"? Do you mean just remove everything after or
return everything after?
=Mid([MemoField], Instr([MemoField],"<DO NOT EDIT>")+14)

If you want everything before, use the Left() function.
--
Duane Hookom
Microsoft Access MVP

I have a memo field which contains at the bottom, specific text following
'<DO NOT EDIT>'. I would like to peel out everything after the '<DO NOT
EDIT>' for reporting. Having trouble trying to format. Any help would be
greatly appreciaited.

Thanks
David

 

[Duane Hookom]

Just give of some examples of your values and what you expect as results.
Rather than use "extract", try "return". This identifies the exact results
you want returned.

Please type in about 4-5 examples and desired outputs.
--
Duane Hookom
Microsoft Access MVP

Thank you. And yes...I meant extract when I stated 'peel out'. This was
working fine. I was just thrown a curve ball. The <DO NOT EDIT> has just
become random. If this string is anywhere with in my text, can I extract
that as well? Meaning, If I have <DO NOT EDIT> at the beginning and the end
of the string I am looking for, can I extract that text? If so, I can Change
the statement at the beginning and end to extract many different strings.
The database we use has limited fields for cutome data. This would gives me
more ways to store and extract what is needed.

What do you mean by "peel out"? Do you mean just remove everything after or
return everything after?
=Mid([MemoField], Instr([MemoField],"<DO NOT EDIT>")+14)

If you want everything before, use the Left() function.
--
Duane Hookom
Microsoft Access MVP

I have a memo field which contains at the bottom, specific text following
'<DO NOT EDIT>'. I would like to peel out everything after the '<DO NOT
EDIT>' for reporting. Having trouble trying to format. Any help would be
greatly appreciaited.

Thanks
David

 

[John W. Vinson]

Thank you. And yes...I meant extract when I stated 'peel out'. This was
working fine. I was just thrown a curve ball. The <DO NOT EDIT> has just
become random. If this string is anywhere with in my text, can I extract
that as well? Meaning, If I have <DO NOT EDIT> at the beginning and the end
of the string I am looking for, can I extract that text? If so, I can Change
the statement at the beginning and end to extract many different strings.
The database we use has limited fields for cutome data. This would gives me
more ways to store and extract what is needed.

If you're storing multiple delimited specific "chunks" of data (and two
qualifies as multiple here!) in a Memo field, you should consider instead
creating a new Comments table, related one to many to your main table, so you
can store each string in *a separate record* in a table, rather than jamming
them all into one field and trying (with much difficulty, as you're seeing) to
untangle them later.

 

[Duane Hookom]

This was my concern also but assumed "The database we use has limited fields
for cutome data" meant it wasn't an option to change. Ideally each data value
should be stored in its own field.

 

[116]

You are correct, and I do agree. But I am limited to fields, and with
limited access to the DB. At this time I will have 2 different groups of
text to extract. My plans are to Start and End each with a unique statement.
<COC1> and <TAG1>. The text in between each of these will variy, as well as
the text at the beginning of the 'memo field'.

David

 

[John Spencer]

You can use Instr to get the start and end points and then do some math.
Personnally, I would write a VBA function to do this using the split function.

This gets the position of the start tag
Instr(1,[MemoField],"StartTag")

This gets the position of the end tag
Instr(1+Instr(1,[MemoField],"StartTag"),[MemoField],"EndTag")

So combining all that into an expression using MID should return the desired
value if it exists. No guarantee that I got the math right or all the
parentheses correctly placed/matched.

MID([MemoField]
,Instr(1,[MemoField],"StartTag")+ Len("StartTag")
,Instr(1+Instr(1,[MemoField],"StartTag"),[MemoField],"EndTag")
-Instr(1,[MemoField],"StartTag") - Len("StartTag"))

To be Safe you should test the memo field to see if the tags exist before you
even try to parse out the string.

IIF([MemoField Like "*StartTag*EndTag*", MID([MemoField]
,Instr(1,[MemoField],"StartTag")+ Len("StartTag")
,Instr(1+Instr(1,[MemoField],"StartTag"),[MemoField],"EndTag")
-Instr(1,[MemoField],"StartTag") - Len("StartTag")),Null)

And that will only handle ONE string. If you had two sets of identical tags,
then the above fails.


John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County