locked
Updating MEMO field in MS Access table using access update query throws extra characters RRS feed

  • Question

  • Hi All,

    I am trying to update field "SUMMARY" which is memo data type field in access table with another field "DETAILS" from antoher table which is also memo data type.

    I am using access update query and query works fine but it adds unrequired extra characters. see example below

    Field DETAIL has data-

    Unlimited stock went for sale live on jan 1st.

    stock worth 25% sold in NJ stores.

    more stock reqired as winter approaching.

    Field SUMMARY after getting updated from field "DETAIL" get data (the underlined characters are unwanted)

    <ul><li>   Unlimited stock went for sale live A&nbsp;-&nbsp on jan 1st.

    <ul><li>   stock worth 25% sold in NJ stores.

    <ul><li>   more stock reqired as winter approaching.

    How shall i update records from one memo field to another memo field without getting this unwanted characters?

                                                       

    Thursday, December 1, 2011 3:51 PM

Answers

  • can you please show me how to use update query with plain text function in query designer?

    Field SUMMARY is Rich text type and field DETAIL is plain text type. I cannot change field DETAIL to plain text type.

    I want to update field DETAIL with data from field SUMMARY.

    Here is the sql syntax for update query which works fine

    UPDATE MAIN INNER JOIN [Project Dashboard] ON MAIN.[Project Number] = [ Project Dashboard].[Project ID] SET MAIN.[DETAIL] = [Project Dashboard].[Summary];

     

    It's about as straightforward as could be:

    UPDATE MAIN INNER JOIN [Project Dashboard] ON MAIN.[Project Number] = [Project Dashboard].[Project ID] SET MAIN.[DETAIL] = PlainText([Project Dashboard].[Summary]);


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by zaveri cc Friday, December 2, 2011 8:16 PM
    Friday, December 2, 2011 7:22 PM

All replies

  • Take a look at the TextFormat properties of the two fields. One of them is probably set to Rich Text, whereas the other is set to Plain Text. Change them both to Plain Text.
    Doug Steele, Microsoft Access MVP
    http://www.AccessMVP.com/djsteele (no e-mails, please!)
    Co-author Access Solutions — Tips, Tricks, and Secrets from Microsoft Access MVPs (ISBN 978-0-470-59168-0)
    Thursday, December 1, 2011 4:15 PM
  • <ul><li>   Unlimited stock went for sale live A&nbsp;-&nbsp on jan 1st.

    <ul><li>   stock worth 25% sold in NJ stores.

    <ul><li>   more stock reqired as winter approaching.

    Hi saveri cc,

    The underlined characters are typically HTML-instructions. Is there a possibility that you convert the DETAILS data to plain text before inserting in SUMMARY?

     

    Imb.

    Thursday, December 1, 2011 4:17 PM
  • Take a look at the TextFormat properties of the two fields. One of them is probably set to Rich Text, whereas the other is set to Plain Text. Change them both to Plain Text.Doug Steele, Microsoft Access MVP

    Or else, in the update query, use the PlainText() function to extract only the plain text from the field that holds the rich text.
    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Proposed as answer by danishani Friday, December 2, 2011 7:09 PM
    Thursday, December 1, 2011 8:14 PM
  • Thanks Douglas for your response.

    I cannot change textformat properties because i have to change it for 77 tables.

    I need update query with plain text function. Thanks

    Friday, December 2, 2011 7:01 PM
  • Thanks Dirk,

    can you please show me how to use update query with plain text function in query designer?

    Field SUMMARY is Rich text type and field DETAIL is plain text type. I cannot change field DETAIL to plain text type.

    I want to update field DETAIL with data from field SUMMARY.

    Here is the sql syntax for update query which works fine

    UPDATE MAIN INNER JOIN [Project Dashboard] ON MAIN.[Project Number] = [ Project Dashboard].[Project ID] SET MAIN.[DETAIL] = [Project Dashboard].[Summary];

     

    Friday, December 2, 2011 7:10 PM
  • can you please show me how to use update query with plain text function in query designer?

    Field SUMMARY is Rich text type and field DETAIL is plain text type. I cannot change field DETAIL to plain text type.

    I want to update field DETAIL with data from field SUMMARY.

    Here is the sql syntax for update query which works fine

    UPDATE MAIN INNER JOIN [Project Dashboard] ON MAIN.[Project Number] = [ Project Dashboard].[Project ID] SET MAIN.[DETAIL] = [Project Dashboard].[Summary];

     

    It's about as straightforward as could be:

    UPDATE MAIN INNER JOIN [Project Dashboard] ON MAIN.[Project Number] = [Project Dashboard].[Project ID] SET MAIN.[DETAIL] = PlainText([Project Dashboard].[Summary]);


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html
    • Marked as answer by zaveri cc Friday, December 2, 2011 8:16 PM
    Friday, December 2, 2011 7:22 PM
  • Thanks Dirk, that was really straight forward.

    Friday, December 2, 2011 8:15 PM