Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Can memo fields be searched for text strings?

Discussion in 'Information Technology' started by Larry Kahm, Jul 28, 2006.

  1. Larry Kahm

    Larry Kahm
    Expand Collapse
    Guest

    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
     
  2. Loading...

    Similar Threads Forum Date
    Opinion Beyond Chamkaur: Wars, Battles & Memories Sikh Sikhi Sikhism Dec 23, 2015
    Memorial to the remarkable Sikh soldiers of WWI Sikh Personalities Oct 31, 2015
    Narayanjot Kaur Aka Spnadmin Some Memories Intellectual Articles Apr 28, 2015
    Gurus Commemorating The Painting Of Guru Gobind Singh Ji By Bhagat Singh Bedi History of Sikhism Apr 15, 2015
    UK UK Sikh Biker Group ‘Humbled’ to be on Lee Rigby Memorial Ride to Woolwich Barracks in Ilford Breaking News May 24, 2014

  3. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Larry Kahm wrote:
    >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.

    --
    Joseph Meehan

    Dia duit
     
  4. Rick Brandt

    Rick Brandt
    Expand Collapse
    Guest

    "Joseph Meehan" <sligojoe_Spamno@hotmail.com> wrote in message
    news:t7S7g.33051$P2.22732@tornado.ohiordc.rr.com...
    > Larry Kahm wrote:
    >>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.


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

    --
    Rick Brandt, Microsoft Access MVP
    Email (as appropriate) to...
    RBrandt at Hunter dot com
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 09 May 2006 00:56:47 GMT, "Larry Kahm"
    <lkahm@nospam_heliotropicsystems.com> wrote:

    >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]
     
  6. Joseph Meehan

    Joseph Meehan
    Expand Collapse
    Guest

    Rick Brandt wrote:
    > "Joseph Meehan" <sligojoe_Spamno@hotmail.com> wrote in message
    > news:t7S7g.33051$P2.22732@tornado.ohiordc.rr.com...
    >> Larry Kahm wrote:
    >>>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.

    >
    > 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.

    --
    Joseph Meehan

    Dia duit
     
  7. Larry Kahm

    Larry Kahm
    Expand Collapse
    Guest

    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" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:d850621mtqgvvp643tgqsqupvg2v907sfd@4ax.com...
    > On Tue, 09 May 2006 00:56:47 GMT, "Larry Kahm"
    > <lkahm@nospam_heliotropicsystems.com> wrote:
    >
    >>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]
     
  8. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Tue, 09 May 2006 14:55:29 GMT, "Larry Kahm"
    <lkahm@nospam_heliotropicsystems.com> wrote:

    >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]
     
  9. Larry Kahm

    Larry Kahm
    Expand Collapse
    Guest

    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" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
    news:s7s26290eerjotmjors0qoc37tei6ro192@4ax.com...
    > On Tue, 09 May 2006 14:55:29 GMT, "Larry Kahm"
    > <lkahm@nospam_heliotropicsystems.com> wrote:
    >
    >>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]
     
  10. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 10 May 2006 14:49:19 GMT, "Larry Kahm"
    <lkahm@nospam_heliotropicsystems.com> wrote:

    >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]
     

Share This Page