Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    I'm attempting to replace text in a memo field using queries. One query [QryTxt] has an ID field and the memo field [DFText]. A second query [QryRV] has the linking ID field, a lookup-value [Tkn] and a replacement-value [Vlu]. The following query almost does what I need -



    SELECT QryTxt.ID,

    Replace(QryTxt.[DFText], QryRV.Tkn, QryRV.Vlu) AS DFText

    FROM

    QryTxt INNER JOIN QryRV ON QryTxt.ID = QryRV.ID;



    Most of the time there is but one lookup-value per ID and the above works great. However, when there are multiple look-up values per ID the query returns one row for each lookup-value with DFText only modified for that single lookup-value no cumulative replacement.



    I've tried to come up with alternative query design approaches which will return a single row with DFText modified by multiple lookup-value replacements, but have been unsuccessful. Between being a query design novice and probably having a case of tunnel vision at this point in looking a the problem, I'm hoping someone one will have an idea on how to design the proper queries.



    In the meantime I'll write a custom function to will loop through the multiple lookup-values and return the modified DFText. My concern with this approach is the extra time it will take to create and read a recordset in vba for each row versus the natural speed of an all query approach.

    Thanks,
    Marty


  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Quote Originally Posted by mcowen View Post
    However, when there are multiple look-up values per ID the query returns one row for each lookup-value with DFText only modified for that single lookup-value no cumulative replacement.
    That is what I would expect with a query, and I can't think of an alternative query design that would do what you want.

    I think you will need to go the VBA approach.

    With VBA could premanently update the memo fields with the replacement text, so you don't need to keep runiing the procedure which could, as you say, take a while. (Depends on how much data there is.)

    Is there a reason to store the data in the current form?
    Regards
    John



  3. #3
    2 Star Lounger
    Join Date
    Jul 2002
    Location
    Sacramento, CA
    Posts
    193
    Thanks
    3
    Thanked 1 Time in 1 Post
    Thanks John.

    Yes the text is part of a "library", i.e a set of tables, of standard boiler-plate text. The text can be assigned, as needed, to multiple reports. Currently there are over 200 token variables which the user can put in the text. When a report with the text is printed the tokens are replaced by acutal table values. Prior to this approach many, many reports were being mantained, by me. Now we have just three reports to handle different format styles and the user is able to maintain the text to stay current with every evolving regulatory requirements. This approach was chosen over a Word based approach because Word was not on all user computers.

    Marty

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •