Parsing a memo field

[Jessie]

Hello,

I am trying to capture the information between 2 strings in a memo field.
This is access 2003.

Example of memo field:
This message is to inform you that the solution for Service Request ID
1-202606020 has been delivered.

The latest updated information:This appears to be the same vulnerability
that was patched with 1355 for OSCE 7.3. This critical patch addresses a
buffer overflow vulnerability in an control that an attacker can exploit.
Date: Aug 12, 2008 Please let me know if you have any questions.

SR Details:
Date Submitted: 08/18/2008 10:06:43
Product:
SR Title: ActiveX Vulnerability
Problem Type: Inquiry

I am trying to use the following syntax in a query to create a new field
with just the information between"latest updated information" and "SR
details",So in the above example I only want:

This appears to be the same vulnerability that was patched with 1355 for
OSCE 7.3. This critical patch addresses a buffer overflow vulnerability in an
control that an attacker can exploit. Date: Aug 12, 2008 Please let me know
if you have any questions.

But i can't get it to work. The information starts in the right place, but i
can't get it to cutoff where "SR details" begins.

Example of syntax I am using in the query:
NewInfo: Mid([Contents],InStr([contents],"The latest updated
information:")+1,InStr([contents],"Details:")-1)

Thank you in advance for your help.

Jessie

 

[Klatuu]

Review the definition of the Mid function in VBA Help. If you don't
specifiy a length, the Mid retuns from the specified location to the end of
the string.
Basically, you have to tell it how many characters to return.

 

[Jessie]

Then that's why it is not working properly since the data between the two
strings is variable. I guess MID won't work in this case...is there another
way to capture just the information between those two strings?

Review the definition of the Mid function in VBA Help. If you don't
specifiy a length, the Mid retuns from the specified location to the end of
the string.
Basically, you have to tell it how many characters to return.

Hello,

I am trying to capture the information between 2 strings in a memo field.
This is access 2003.

Example of memo field:
This message is to inform you that the solution for Service Request ID
1-202606020 has been delivered.

The latest updated information:This appears to be the same vulnerability
that was patched with 1355 for OSCE 7.3. This critical patch addresses a
buffer overflow vulnerability in an control that an attacker can exploit.
Date: Aug 12, 2008 Please let me know if you have any questions.

SR Details:
Date Submitted: 08/18/2008 10:06:43
Product:
SR Title: ActiveX Vulnerability
Problem Type: Inquiry

I am trying to use the following syntax in a query to create a new field
with just the information between"latest updated information" and "SR
details",So in the above example I only want:

This appears to be the same vulnerability that was patched with 1355 for
OSCE 7.3. This critical patch addresses a buffer overflow vulnerability in
an
control that an attacker can exploit. Date: Aug 12, 2008 Please let me
know
if you have any questions.

But i can't get it to work. The information starts in the right place, but
i
can't get it to cutoff where "SR details" begins.

Example of syntax I am using in the query:
NewInfo: Mid([Contents],InStr([contents],"The latest updated
information:")+1,InStr([contents],"Details:")-1)

Thank you in advance for your help.

Jessie

 

[mcescher]

Hello,

I am trying to capture the information between 2 strings in a memo field.
This is access 2003.

Example of memo field:
This message is to inform you that the solution for Service Request ID
1-202606020 has been delivered.

The latest updated information:This appears to be the same vulnerability
that was patched with 1355 for OSCE 7.3. This critical patch addresses a
buffer overflow vulnerability in an control that an attacker can exploit.
Date: Aug 12, 2008 Please let me know if you have any questions.

SR Details:
Date Submitted: 08/18/2008 10:06:43
Product:
SR Title: ActiveX Vulnerability
Problem Type: Inquiry

I am trying to use the following syntax in a query to create a new field
with just the information between"latest updated information" and "SR
details",So in the above example I only want:

This appears to be the same vulnerability that was patched with 1355 for
OSCE 7.3. This critical patch addresses a buffer overflow vulnerability in an
control that an attacker can exploit. Date: Aug 12, 2008 Please let me know
if you have any questions.

But i can't get it to work. The information starts in the right place, but i
can't get it to cutoff where "SR details" begins.

Example of syntax I am using in the query:
NewInfo: Mid([Contents],InStr([contents],"The latest updated
information:")+1,InStr([contents],"Details:")-1)

Thank you in advance for your help.

Jessie

You're very close.

Public Function LatestInfo(strContents as String)
Dim intStart as Integer
Dim intEnd as Integer
Dim intLength as Integer
intStart = InStr(strContents ,"The latest updated information:") + 1
intEnd = InStr(strContents ,"Details:") - 1
intLength = intEnd - intStart
LatestInfo = Mid(strContents, intStart, intLength)
End Function

Add some error checking and you're good to go.

Hope this helps,
Chris M .

 

[Klatuu]

Not really, you have to have some way to know when to stop.

Then that's why it is not working properly since the data between the two
strings is variable. I guess MID won't work in this case...is there
another
way to capture just the information between those two strings?

Review the definition of the Mid function in VBA Help. If you don't
specifiy a length, the Mid retuns from the specified location to the end
of
the string.
Basically, you have to tell it how many characters to return.

Hello,

I am trying to capture the information between 2 strings in a memo
field.
This is access 2003.

Example of memo field:
This message is to inform you that the solution for Service Request ID
1-202606020 has been delivered.

The latest updated information:This appears to be the same
vulnerability
that was patched with 1355 for OSCE 7.3. This critical patch addresses
a
buffer overflow vulnerability in an control that an attacker can
exploit.
Date: Aug 12, 2008 Please let me know if you have any questions.

SR Details:
Date Submitted: 08/18/2008 10:06:43
Product:
SR Title: ActiveX Vulnerability
Problem Type: Inquiry

I am trying to use the following syntax in a query to create a new
field
with just the information between"latest updated information" and "SR
details",So in the above example I only want:

This appears to be the same vulnerability that was patched with 1355
for
OSCE 7.3. This critical patch addresses a buffer overflow vulnerability
in
an
control that an attacker can exploit. Date: Aug 12, 2008 Please let me
know
if you have any questions.

But i can't get it to work. The information starts in the right place,
but
i
can't get it to cutoff where "SR details" begins.

Example of syntax I am using in the query:
NewInfo: Mid([Contents],InStr([contents],"The latest updated
information:")+1,InStr([contents],"Details:")-1)

Thank you in advance for your help.

Jessie

 

[Jessie]

Hi Chris,

thank you for helping. I am not sure how to use the public function. If i
want to create a new field with the results of the function how would i do
that?

Jessie

Hello,

I am trying to capture the information between 2 strings in a memo field.
This is access 2003.

Example of memo field:
This message is to inform you that the solution for Service Request ID
1-202606020 has been delivered.

The latest updated information:This appears to be the same vulnerability
that was patched with 1355 for OSCE 7.3. This critical patch addresses a
buffer overflow vulnerability in an control that an attacker can exploit.
Date: Aug 12, 2008 Please let me know if you have any questions.

SR Details:
Date Submitted: 08/18/2008 10:06:43
Product:
SR Title: ActiveX Vulnerability
Problem Type: Inquiry

I am trying to use the following syntax in a query to create a new field
with just the information between"latest updated information" and "SR
details",So in the above example I only want:

This appears to be the same vulnerability that was patched with 1355 for
OSCE 7.3. This critical patch addresses a buffer overflow vulnerability in an
control that an attacker can exploit. Date: Aug 12, 2008 Please let me know
if you have any questions.

But i can't get it to work. The information starts in the right place, but i
can't get it to cutoff where "SR details" begins.

Example of syntax I am using in the query:
NewInfo: Mid([Contents],InStr([contents],"The latest updated
information:")+1,InStr([contents],"Details:")-1)

Thank you in advance for your help.

Jessie

You're very close.

Public Function LatestInfo(strContents as String)
Dim intStart as Integer
Dim intEnd as Integer
Dim intLength as Integer
intStart = InStr(strContents ,"The latest updated information:") + 1
intEnd = InStr(strContents ,"Details:") - 1
intLength = intEnd - intStart
LatestInfo = Mid(strContents, intStart, intLength)
End Function

Add some error checking and you're good to go.

Hope this helps,
Chris M .