Can memo fields be searched for text strings?

[Larry Kahm]

I have a client who has an application that was designed to use boilerplate
text in several memo fields.

His staff customize some of the text, as necessary, each time a new document
is prepared.

At some point, he'd like to search through the database to find records
with a certain set of characteristics - which just happen to be encoded in
the text of the memo fields.

What is an effective way of creating a set of search fields for these memo
fields?

Thanks!

Larry

 

[Joseph Meehan]

I have a client who has an application that was designed to use
boilerplate text in several memo fields.

His staff customize some of the text, as necessary, each time a new
document is prepared.

At some point, he'd like to search through the database to find
records with a certain set of characteristics - which just happen to
be encoded in the text of the memo fields.

What is an effective way of creating a set of search fields for these
memo fields?

Thanks!

Larry

As I recall it can search only the first 254 characters.

 

[Rick Brandt]

As I recall it can search only the first 254 characters.

I believe you can only *sort* on the first 255 characters. You should be able
to search all of them (not very efficiently though).

 

[John Vinson]

What is an effective way of creating a set of search fields for these memo
fields?

For certain values of "effective", you can just use a wildcard search:
searching a memo field using a criterion such as

LIKE "*potato*"

will find all records with potato anywhere within the memo field.

It won't use indexes and must do a full table scan, *and* a full scan
of the memo text for every record, so it can be slow - but it will
find the data.

John W. Vinson[MVP]

 

[Joseph Meehan]

I believe you can only *sort* on the first 255 characters. You
should be able to search all of them (not very efficiently though).

Thanks for the correction. I was not sure of that and likely mixed up
the two.

 

[Larry Kahm]

John,

The information you provided makes quite a lot of sense; no index results in
table scan (sheesh!).

One alternative that was proposed was to put the text into Microsoft Word
templates and use OLE to modify it.

However, having said that, I don't think my client is going to have a viable
solution to searching through those fields.

Thanks for your assistance! Now to build those very slow responding search
forms. I guess I can always use a "crawl bar"...

Larry

 

[John Vinson]

John,

The information you provided makes quite a lot of sense; no index results in
table scan (sheesh!).

One alternative that was proposed was to put the text into Microsoft Word
templates and use OLE to modify it.

However, having said that, I don't think my client is going to have a viable
solution to searching through those fields.

Thanks for your assistance! Now to build those very slow responding search
forms. I guess I can always use a "crawl bar"...

Just one possible thought: if these terms have independent validity as
attributes of the table Entity, maybe they should be stored in regular
(indexed!) text fields rather than buried in a Memo. Could you perhaps
construct the memo text by concatenating pieces, rather than violating
atomicity as you're now doing?

John W. Vinson[MVP]

 

[Larry Kahm]

John,

Regrettably, these fields can't be defined as text. The "boilerplate"
strings are too long to fit in 255 bytes, and the amount of customization -
specifically adding text, based on a project - that a user could possibly
make would preclude it.

Nice thought though!

Of course, the client casually mentioned that he requires all of the text to
print on one physical page. So I'm going to be playing with font sizes in
the report for the rest of my life....

Larry

 

[John Vinson]

Of course, the client casually mentioned that he requires all of the text to
print on one physical page....

and of course that the users must be permitted to enter any text they
wish without any constraints or error messages. Scott Adams take note!

Arial font, size 2... and distribute free magnifying glasses to the
users... <g>

John W. Vinson[MVP]