Access 2000 - Loop through a Memo field with VBA

[Tony_VBACoder]

I have a situation where we scan in documents and store the full text of the
document in a Memo field. Now, I need to write a VBA looping routine that
will loop through each character of the Memo field looking for various pieces
of information.

What is the best method for doing this? Are there limitations on trying to
read the entire Memo field into a string variable, or some other variable
type?

 

[Douglas J. Steele]

You shouldn't have any problem assigning the content of the field to a
string variable, and using the various Text functions.

 

[Dirk Goldgar]

I have a situation where we scan in documents and store the full text
of the document in a Memo field. Now, I need to write a VBA looping
routine that will loop through each character of the Memo field
looking for various pieces of information.

What is the best method for doing this? Are there limitations on
trying to read the entire Memo field into a string variable, or some
other variable type?

You can assign the field's value to a string variable, and use code like
this:

Dim strText As STring
Dim I As Long

strText = Nz(Me!MyMemoField, "")

For I = 1 To Len(strText)

If Mid(strText, I, 9) = "Something" Then
' found "Something" ...
End If

Next I

However, if you're looking for specific strings, it will be a *lot*
faster to use the InStr() function to locate them:

Dim strText As STring
Dim I As Long

strText = Nz(Me!MyMemoField, "")

I = InStr(strText, "Something")
If I > 0 Then
' found "Something" ...
End If

 

[Tony_VBACoder]

Dirk and Doug, thanks for the assistance. I had the looping routine written,
but was concerned about string variables being truncated because memo fields
can be virtually anything. This brings me to Dirk's suggestion of using the
Instr function. Won't Access "choke" using Instr on a memo field because a
memo field could be virtually anything?

Also, in my situation, Instr, will not work, because I have to find many
instances of various values and then whatever follows that value. For
instance, I have to search for FirstName and then whatever is after
FirstName, to come up with the person's first name. But yes, that was a
great suggestion.

 

[Douglas J. Steele]

What do you mean by "virtually anything"? I thought you said you were
storing full text in the Memo field.

A string variable can hold approximately 2 billion characters (2^31), so you
shouldn't have any problem as long as the Memo field does contain text.

 

[Dirk Goldgar]

Dirk and Doug, thanks for the assistance. I had the looping routine
written, but was concerned about string variables being truncated
because memo fields can be virtually anything. This brings me to
Dirk's suggestion of using the Instr function. Won't Access "choke"
using Instr on a memo field because a memo field could be virtually
anything?

I don't follow you. A memo field will always contain text, though it
might be in "rich text format".

Also, in my situation, Instr, will not work, because I have to find
many instances of various values and then whatever follows that
value. For instance, I have to search for FirstName and then
whatever is after FirstName, to come up with the person's first name.
But yes, that was a great suggestion.

I don't see the problem. You can search repeatedly for the same or
different strings, and once you've found the occurrence you want, you
can scan forward from that position for a delimiting character of some
sort.

Note that the InStr() function has an optional <Start> argument, which
can be used to specify the starting point for the search, within the
text being searched. That lets you keep searching for new occurrences
until there aren't any more, and it lets you search for (e.g.) the end
of the first name, once you've found the word "FirstName".