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 shouldn't have any problem assigning the content of the field to a
string variable, and using the various Text functions.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Tony_VBACoder" <(E-Mail Removed)> wrote in message
news:4D69D3F6-3718-4CC2-8E23-(E-Mail Removed)...
>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?

"Tony_VBACoder" <(E-Mail Removed)> wrote in
message news:4D69D3F6-3718-4CC2-8E23-(E-Mail Removed)
> 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

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

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.

"Dirk Goldgar" wrote:

> "Tony_VBACoder" <(E-Mail Removed)> wrote in
> message news:4D69D3F6-3718-4CC2-8E23-(E-Mail Removed)
> > 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
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
>

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.

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)


"Tony_VBACoder" <(E-Mail Removed)> wrote in message
news:61E7C03A-FA33-4307-BDB9-(E-Mail Removed)...
> 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.
>
> "Dirk Goldgar" wrote:
>
>> "Tony_VBACoder" <(E-Mail Removed)> wrote in
>> message news:4D69D3F6-3718-4CC2-8E23-(E-Mail Removed)
>> > 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
>>
>> --
>> Dirk Goldgar, MS Access MVP
>> www.datagnostics.com
>>
>> (please reply to the newsgroup)
>>
>>
>>

"Tony_VBACoder" <(E-Mail Removed)> wrote in
message news:61E7C03A-FA33-4307-BDB9-(E-Mail Removed)
> 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".

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)