Results 1 to 4 of 4
  1. #1
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86

    Unhappy Access SQL update query truncating comments (long text -> short text) [Access 2016]


    When I run a sql update query to update a comments field in a table from a import sheet, it truncates the comment to 255 characters even though both of the relevant fields in each table are set to long text.

    It's almost as if they're converting the text to short text in the update. I have checked before the update and there were indeed specific entries where a comment had far more than 255 characters that became truncated to 255 after the update.

    The code looks like this:

    Code:
    Update table AS T 
    Inner Join import_sheet AS I
    ON T.Key = I.Key
    SET
    T.field1 = I.field1
    T.field2 = I.field2
    ...
    WHERE
    T.user = 'user1' Or
    T.user = 'user2' Or
    ...
    ;
    Any idea on what's going on and how I can work around this issue if it's not fixable?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    this has long been the bane of Access users. You'd think mSoft would have corrected it long ago, but no.
    I think the only way I managed to get all characters in a long text, was to export directly from the table.
    Queries always truncated.

    I dont know how an update to a memo field can be done, since its a query.
    Google this. there may be a workaround.

    (thanks Microsoft)

  3. #3
    TerraEarth is offline Advanced Beginner
    Windows 10 Access 2016
    Join Date
    Mar 2018
    Posts
    86
    Oh dear...

  4. #4
    isladogs's Avatar
    isladogs is offline MVP / VIP
    Windows 10 Access 2010 32bit
    Join Date
    Jan 2014
    Location
    Somerset, UK
    Posts
    5,954
    I don't think the previous response was very helpful
    There are lots of reasons why long text / memo fields can get truncated but if you are aware of the reasons, you should be able to solve your problem.
    Allen Browne has a very detailed answer here: http://allenbrowne.com/ser-63.html.
    When you read that article, I expect it will make sense as to why the issue hasn't been fixed 'globally'

    Good luck and don't despair
    Colin, Access MVP, Website, email
    The more I learn, the more I know I don't know. When I don't know, I keep quiet!
    If I don't know that I don't know, I don't know whether to answer

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Access 2013 truncating Long Text fields in Reports
    By TundraMonkey in forum Reports
    Replies: 13
    Last Post: 04-18-2017, 08:11 PM
  2. Replies: 1
    Last Post: 05-18-2016, 09:46 AM
  3. Replies: 6
    Last Post: 04-09-2015, 08:57 PM
  4. Convert Short text column to date ms access
    By SameerSarswat in forum Access
    Replies: 3
    Last Post: 10-09-2014, 10:42 AM
  5. Long Text field in an Update query
    By Dave D in forum Queries
    Replies: 2
    Last Post: 08-03-2014, 12:10 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums