Extracting information from a memo field

[GLT]

Hi,

I have a memo field (which is originally an imported email from Outlook).

I would like to scan through each memo field, extract certain information
and add it to new fields, and then delete the memo field.

Contents in the memo field look like this:

Body
"NDMEXFK01: [JobID:168 Group 2] Operation Incomplete.
Number of Error(s)/Warning(s): 7/47
If you have received this email in error, please notify the sender
immediately and erase all copies of the email and any attachments to it. The
information contained in this email and any attachments may be private,
confidential and legally privileged or the subject of copyright. If you are
not the addressee it may be illegal to review, disclose, use, forward, or
distribute this email and/or its contents ... blah blah blah

Unless otherwise specified, the information in the email and any attachments
is intended as a guide only and should not be relied upon as legal or
technical advice or regarded as a substitute for legal or technical advice in
individual cases. Opinions contained in this email or any of its attachments
do not necessarily reflect blah blah blah .....
"

Out of the above message, I’m only interested in:

1) Anything between the first " (quote) and : (colon)
in the example above, this would be:
NDMEXFK01

2) Anything between the square brackets
in the example above, this would be:
JobID: 168 Group 2

3) Operation status
in the example above, this would be:
Operation Incomplete.

4) Number of errors/warnings:
in the example above, this would be:
7/47

All of these values can vary in length. Can anyone advise what the best way
to extract this data (and move to a new field) would be?

Would this be faster being processed by a query or by VBA?

Any help would be greatly appreciated…

Cheers,
GLT.

 

[Cheese_whiz]

Hi GLT,

Take a look at the string functions here:
http://www.techonthenet.com/access/functions/index.php

Look over them all, but I think of particular interest might be the 'InStr'
function and the "Mid" function.

I'd probably do this in vba, but that really depends on where the data is
and what you want to do with it. In vba, though, it's a little easier to
work with since playing with code in the query design view is a bit of a pain
since you invariably can't see it all and have to resize columns or scroll.

The idea will be this (based on the example with the []s):

1. Declare a string variable and assign the value of the memo field to it.
This may not be absolutely necessary, but it's what I'd do. I'll call it
strMemo. Declare two other variables, one for character in the memo that
proceeds the beginning of the string you want to extract, and one of the
ending character. In this case, it's the brackets. I'm calling the
variables intOpenBracket and intCloseBracket. They are both integers. One
more variable for the final string (strBracketPhrase)

2. Use InStr to locate the position of the first bracket in strMemo. That
will give you some number. Save that number to a variable (intOpenBracket).
So, your first calculation will be

strOpenBracket = InStr(1, strMemo, "[")

3. Add one to that variable. That gets you the position of the first
character in the string you want to extract since you don't want to include
the bracket.

strOpenBracket = strOpenBracket + 1

4. Use that as the starting point in another 'InStr' to find the second
(closing) bracket. Store that number in a variable:

strClosedBracket = InStr(strOpenBracket, strMemo, "]")

5. Subtract one from that number to get the position of the last character
in the string you want to extract:

strClosedBracket = strClosedBracket - 1

6. Use the 'Mid' function and your two stored numbers to pull out the
string you want:

strBracketPhrase = Mid(strMemo, intOpenBracket, intClosedBracket)

Put it all together and you get this:
_____________________________________
Sub SomeButton_Click()
Dim strMemo as String
Dim intOpenBracket as Integer
Dim intClosedBracket as Integer
Dim strBracketPhrase as String

strOpenBracket = InStr(1, strMemo, "[")
strOpenBracket = strOpenBracket + 1

strClosedBracket = InStr(strOpenBracket, strMemo, "]")
strClosedBracket = strClosedBracket - 1

strBracketPhrase = Mid(strMemo, intOpenBracket, intClosedBracket)

'Add Code here to do something with the string now that it's extracted

End Sub
_______________________________________

Add your own error handler, and don't forget to add code to do something
with the string you extracted. Of course, I just set that up on a button but
you may want it on some other event.

It's important to note that using string functions like this depends on
there not being other instances of the brackets (or whatever you are using to
mark the beginning/end of the string you want to extract) in the memo prior
to the string you want to extract. If there are, it will throw all the
numbers off.

Also, this assumes that the button will be located on a form that displays
the memo field. You could easily use something like 'DLookup' to get the
contents of the memo field and assign them to the variable (if the memo field
isn't on the form where you are running this code).

Finally, in my example I used one character as the string to search for in
the InStr function. As I stated, that means that if that character shows up
earlier in the memo than right before the string you want to extract, it will
mess things up. Bare in mind, though, that you CAN search for a longer
string than just one character. That will make it a lot less likely that the
function will find the wrong string. Of course, you'll have to modify the
adjustment you use (the '-1' and '+1' I used) because the position returned
using a string won't be just 1 away from the string you want to extract.

I hope that works. I'm always a little leary about writing code for
responses because I usually have to tweak things a little in my own code. I
do know that using string functions like this works because I've done it in
the past.

I hope that helps,
CW

Hi,

I have a memo field (which is originally an imported email from Outlook).

I would like to scan through each memo field, extract certain information
and add it to new fields, and then delete the memo field.

Contents in the memo field look like this:

Body
"NDMEXFK01: [JobID:168 Group 2] Operation Incomplete.
Number of Error(s)/Warning(s): 7/47
If you have received this email in error, please notify the sender
immediately and erase all copies of the email and any attachments to it. The
information contained in this email and any attachments may be private,
confidential and legally privileged or the subject of copyright. If you are
not the addressee it may be illegal to review, disclose, use, forward, or
distribute this email and/or its contents ... blah blah blah

Unless otherwise specified, the information in the email and any attachments
is intended as a guide only and should not be relied upon as legal or
technical advice or regarded as a substitute for legal or technical advice in
individual cases. Opinions contained in this email or any of its attachments
do not necessarily reflect blah blah blah .....
"

Out of the above message, I’m only interested in:

1) Anything between the first " (quote) and : (colon)
in the example above, this would be:
NDMEXFK01

2) Anything between the square brackets
in the example above, this would be:
JobID: 168 Group 2

3) Operation status
in the example above, this would be:
Operation Incomplete.

4) Number of errors/warnings:
in the example above, this would be:
7/47

All of these values can vary in length. Can anyone advise what the best way
to extract this data (and move to a new field) would be?

Would this be faster being processed by a query or by VBA?

Any help would be greatly appreciated…

Cheers,
GLT.

 

[John W. Vinson]

Out of the above message, I’m only interested in:

1) Anything between the first " (quote) and : (colon)
in the example above, this would be:
NDMEXFK01

FirstBit: Mid([memofield], InStr([memofield], '"') + 1, InStr([memofield],
":") - 1)

2) Anything between the square brackets
in the example above, this would be:
JobID: 168 Group 2

SecondBit: Mid([memofield], InStr([memofield], "[") + 1, InStr([memofield],
"]") - 1

3) Operation status
in the example above, this would be:
Operation Incomplete.

Assuming that this is the text between the first closebracket and the first
carriage return,

ThirdBit: Mid([memofield], InStr([memofield], "]") + 1, InStr([memofield],
Chr(13) & Chr(10)) - 2)

4) Number of errors/warnings:
in the example above, this would be:
7/47

left as an exercise...

 

[Cheese_whiz]

We're both MVPs. My 'V' just happens to stand for 'verbose'....



CW

Out of the above message, I’m only interested in:

1) Anything between the first " (quote) and : (colon)
in the example above, this would be:
NDMEXFK01

FirstBit: Mid([memofield], InStr([memofield], '"') + 1, InStr([memofield],
":") - 1)

2) Anything between the square brackets
in the example above, this would be:
JobID: 168 Group 2

SecondBit: Mid([memofield], InStr([memofield], "[") + 1, InStr([memofield],
"]") - 1

3) Operation status
in the example above, this would be:
Operation Incomplete.

Assuming that this is the text between the first closebracket and the first
carriage return,

ThirdBit: Mid([memofield], InStr([memofield], "]") + 1, InStr([memofield],
Chr(13) & Chr(10)) - 2)

4) Number of errors/warnings:
in the example above, this would be:
7/47

left as an exercise...

 

[John Vinson]

We're both MVPs. My 'V' just happens to stand for 'verbose'....

<snork!>

Well, maybe I'll start signing myself

John W. Vinson[MTP]

for "terse"...

Good explanation by the way, and thanks!!

 

[GLT]

Thanks for both your responses - I really appreciate your help with this.

I have it all working except the part where I chop out the JobID:xxxx (where
xxxx is a number), fails if the number is zero (ie. JobID:0).

This is the code that I use to extract it with:

JobID:
Left((Mid([body],(InStr([Body],"[")+1),((InStr([Body],"]")-1)-(InStr([Body],"[")+1)))),InStr((Mid([body],(InStr([Body],"[")+1),((InStr([Body],"]")-1)-(InStr([Body],"[")+1))))," ")-1)

Would you be able to advise where I am going wrong?

Cheers,
GLT

We're both MVPs. My 'V' just happens to stand for 'verbose'....

<snork!>

Well, maybe I'll start signing myself

John W. Vinson[MTP]

for "terse"...

Good explanation by the way, and thanks!!

 

[Cheese_whiz]

Hi GLT,

I think the problem is that when you only have a string of 1 character in
length, then when you calculate the position of the first bracket and add 1
to it, and then calculate the position of the second bracket and take 1 away
from it, you get the same position number. If you subtract one from the
other you get 0 (zero) as the length of string you want to extract.

You could wrap the second part of your Left function (is the Left function
even necessary here?) in an IIF function to test the value and if it's 0 make
it 1.

I think that would do it.

CW

Thanks for both your responses - I really appreciate your help with this.

I have it all working except the part where I chop out the JobID:xxxx (where
xxxx is a number), fails if the number is zero (ie. JobID:0).

This is the code that I use to extract it with:

JobID:
Left((Mid([body],(InStr([Body],"[")+1),((InStr([Body],"]")-1)-(InStr([Body],"[")+1)))),InStr((Mid([body],(InStr([Body],"[")+1),((InStr([Body],"]")-1)-(InStr([Body],"[")+1))))," ")-1)

Would you be able to advise where I am going wrong?

Cheers,
GLT

We're both MVPs. My 'V' just happens to stand for 'verbose'....

<snork!>

Well, maybe I'll start signing myself

John W. Vinson[MTP]

for "terse"...

Good explanation by the way, and thanks!!

 

[GLT]

Hi Cheese,

Thanks for your reply, can I rewrite this statement to make it any easier?

I built this from the QBE query and I would like to know if I can make it
simpler before adding the Iif statement...

Cheers,
GLT.

Hi GLT,

I think the problem is that when you only have a string of 1 character in
length, then when you calculate the position of the first bracket and add 1
to it, and then calculate the position of the second bracket and take 1 away
from it, you get the same position number. If you subtract one from the
other you get 0 (zero) as the length of string you want to extract.

You could wrap the second part of your Left function (is the Left function
even necessary here?) in an IIF function to test the value and if it's 0 make
it 1.

I think that would do it.

CW

Thanks for both your responses - I really appreciate your help with this.

I have it all working except the part where I chop out the JobID:xxxx (where
xxxx is a number), fails if the number is zero (ie. JobID:0).

This is the code that I use to extract it with:

JobID:
Left((Mid([body],(InStr([Body],"[")+1),((InStr([Body],"]")-1)-(InStr([Body],"[")+1)))),InStr((Mid([body],(InStr([Body],"[")+1),((InStr([Body],"]")-1)-(InStr([Body],"[")+1))))," ")-1)

Would you be able to advise where I am going wrong?

Cheers,
GLT

:

We're both MVPs. My 'V' just happens to stand for 'verbose'....


<snork!>

Well, maybe I'll start signing myself

John W. Vinson[MTP]

for "terse"...

Good explanation by the way, and thanks!!

 

[Cheese_whiz]

Hi GLT,

I'll leave it to you to figure out if you can make that statement shorter.
My initial thinking was that if you used Mid on the outside instead of Left,
you might save a little work. I don't know that to be true, but it's my best
guess on first blush.

In terms of making it 'easier', I just find things easier to work with if
the statements aren't too long. John's code was an improvement over mine,
imo, because it was BOTH more succinct AND still in highly manageable
statements.

That's just, of course, my opinion. More experienced developers may
completely disagree and I wouldn't be surprised at all.

Good luck,
CW

Hi Cheese,

Thanks for your reply, can I rewrite this statement to make it any easier?

I built this from the QBE query and I would like to know if I can make it
simpler before adding the Iif statement...

Cheers,
GLT.

Hi GLT,

I think the problem is that when you only have a string of 1 character in
length, then when you calculate the position of the first bracket and add 1
to it, and then calculate the position of the second bracket and take 1 away
from it, you get the same position number. If you subtract one from the
other you get 0 (zero) as the length of string you want to extract.

You could wrap the second part of your Left function (is the Left function
even necessary here?) in an IIF function to test the value and if it's 0 make
it 1.

I think that would do it.

CW

Thanks for both your responses - I really appreciate your help with this.

I have it all working except the part where I chop out the JobID:xxxx (where
xxxx is a number), fails if the number is zero (ie. JobID:0).

This is the code that I use to extract it with:

JobID:
Left((Mid([body],(InStr([Body],"[")+1),((InStr([Body],"]")-1)-(InStr([Body],"[")+1)))),InStr((Mid([body],(InStr([Body],"[")+1),((InStr([Body],"]")-1)-(InStr([Body],"[")+1))))," ")-1)

Would you be able to advise where I am going wrong?

Cheers,
GLT

:

:

We're both MVPs. My 'V' just happens to stand for 'verbose'....


<snork!>

Well, maybe I'll start signing myself

John W. Vinson[MTP]

for "terse"...

Good explanation by the way, and thanks!!