Using Memo Fields in VBA

  • Thread starter Thread starter Aljrob
  • Start date Start date
A

Aljrob

Dear All,

I'm currently writing a db to analyse automated responses
generated when a client logs on to a company website. The
data is held originally in a MS Exchange server folder. I
have successfully created a link table to import the
emails in to Access. However, I'm having some trouble
successfully getting the data out of the body field of
the email - the body is stored correctly as a memo field.

What I need is a method of going through every line of
the memo field and extracting the relevant data. I am up-
to-speed enough to be able to handle storing the
retrieved data myself, but am stumped as to how I can
access the memo field line by line.

For the sake of argument, let's say that the memo field
of interest is in a recordset, rs.fields("Body")

An example of the contents of the memo field is shown
below:


Date : on 21 of 09 2004 at 22:40 GMTError:



Operating System
=================
Passed? = No.
OS = Macintosh
Platform = MacPPC

Browser
========
Passed? = Yes.
Type = IE
Version = 5.23
Major Version = 5
Minor Version = 0.23
Build Version = null
Service Packs = 0

Screen Resolution
==================
Width x Height = 1280 x 854

JavaScript
===========
Passed? = Yes.
Supported? = Yes.
Enabled? = Yes.
Version = 1.3
Build = null

Java
=====
Passed? = No.
Enabled? = Yes.
Applets? = Yes.
Vendor = null
Version = null
MS Build = null
Plugin Version = 1.3.1

Cookies
========
Passed? = Yes.
Session Enabled? = Yes.
Persistent Enabled? = Yes.
 
The line delimiter in a text or memo field is <CR><LF> - you can use
vbCRLF in VBA. If you really need to search "line by line", you will
need to write a function to return one line (all text up to the next
vbCRLF) at a time. Usually what you need to do in this sort of
situation is search for a characteristic string that is related to
each datum you are trying to extract, e.g. search for "Operating
System", then "Passed?= ", and evaluate the string that follows
"Passed?= ".

Dear All,

I'm currently writing a db to analyse automated responses
generated when a client logs on to a company website. The
data is held originally in a MS Exchange server folder. I
have successfully created a link table to import the
emails in to Access. However, I'm having some trouble
successfully getting the data out of the body field of
the email - the body is stored correctly as a memo field.

What I need is a method of going through every line of
the memo field and extracting the relevant data. I am up-
to-speed enough to be able to handle storing the
retrieved data myself, but am stumped as to how I can
access the memo field line by line.

For the sake of argument, let's say that the memo field
of interest is in a recordset, rs.fields("Body")

An example of the contents of the memo field is shown
below:


Date : on 21 of 09 2004 at 22:40 GMTError:



Operating System
=================
Passed? = No.
OS = Macintosh
Platform = MacPPC

Browser
========
Passed? = Yes.
Type = IE
Version = 5.23
Major Version = 5
Minor Version = 0.23
Build Version = null
Service Packs = 0

Screen Resolution
==================
Width x Height = 1280 x 854

JavaScript
===========
Passed? = Yes.
Supported? = Yes.
Enabled? = Yes.
Version = 1.3
Build = null

Java
=====
Passed? = No.
Enabled? = Yes.
Applets? = Yes.
Vendor = null
Version = null
MS Build = null
Plugin Version = 1.3.1

Cookies
========
Passed? = Yes.
Session Enabled? = Yes.
Persistent Enabled? = Yes.
Click to expand...


Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Hi,

I'd probably do something like this (air code):

Dim arLines As Variant
Dim j As Long
Dim strLine As String

arLines = Split vbLF, Replace(rs.Fields("Body"), vbCRLF, vbLF)

For j = 0 to UBound(arLines)
strLine = arLines(j)

Select Case strLine
Case "Operating System"
...
End Select
Next
 
Back
Top