Results 1 to 15 of 15
  1. #1
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Memo Field Truncation (A2K SR1)

    I have a Memo field which I use in a query. The Memo field in my table contains, for example, 3,000 characters. However, the query returns only 1,122 (literally) characters of the 3,000. The table's Memo field is unformatted and has been confirmed to contain all of the desired content. Does anyone know what's happening? Why is the query returning such a strange number of characters? How can I avoid the truncation completely, because I need the complete content of the Memo field to be returned in my query? Thanks for your help.

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Memo Field Truncation (A2K SR1)

    How do you know that the Memo field contains 3000 characters? And is your query an Access query? Are you looking at the data in Access, or are you bringing it into Excel?

    If you are looking at it in Excel, You can only import a maximum of about 1000 characters reliably. Otherwise, if you are looking at the query in Access and doing a len(fieldname) as a calculated value, and it says 3000, but when you count the characters returned in fieldname you get 1122, then something is definitely wacky with your MDB file. Normal queries do not truncate Memo fields when you display them.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo Field Truncation (A2K SR1)

    Wendell,

    I'm working totally in Access. I counted the number of characters using the len(fieldname) function and it returns 3,000 characters, but the query only returns 1,122 characters. Geez, I hate when this stuff happens. Thanks for you insight.

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Memo Field Truncation (A2K SR1)

    You might try importing everything into a brand new empty A2K database and see if the behavior changes. Memo fields are one of the primary sources of corruption in Jet tables. Can you see 3000 characters when you look at the table?
    Wendell

  5. #5
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo Field Truncation (A2K SR1)

    Wendell,

    I think I'll give that a try. Yes, I can see all 3,000 characters in the table field.

  6. #6
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo Field Truncation (A2K SR1)

    Wendell,

    Post Nos. 23268 and 59222 describe the same problem, but no solutions were posted. I'm glad I'm not alone.

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Memo Field Truncation (A2K SR1)

    A couple of questions - are you trying to sort on the memo field?
    Also, is there any kind of function or formatting associated with the memo field?
    Is your query a Union query?
    Any of these can cause the kind of problem you are experiencing. The other posts had some of these characteristics..
    Wendell

  8. #8
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo Field Truncation (A2K SR1)

    No sorting, no formatting, no function, but it IS a Union query.

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Memo Field Truncation (A2K SR1)

    Therein lies your problem. If possible, I always avoid UNION queries. We generally use a series of append queries to make a temporary table with the desired results. I know it's more work, but actually what we usually do is put all the records in a single table to begin with, and if they need to be partitioned for some reason, we put in a new field to use in partitioning the data when necessary.

    That aside, I have a hunch that what is happening is that your record with 3000 characters in the memo field is in one of the later tables in the UNION. This is strictly a guess, but perhaps what happens is that Jet looks at the results of the first SELECT clause and sets a maximum size of the memo field to be the largest of the records returned. Any larger fields in subsequent SELECT clauses then get truncated to that maximum value. Is it a bug - I would say so. Has it been fixed - have you downloaded the latest patches for Jet 4.0? I haven't, but you might want to just to see if the behavior changes. Hope this helps.
    Wendell

  10. #10
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo Field Truncation (A2K SR1)

    Wendell,

    I don't know if this helps, but I ran a few tests and got the following results:

    * I created a record with 806 characters in the Memo field.
    * I have a 'base' query that feeds the Union query. This 'base' query, which includes the Memo field, returns all 806 characters.
    * The Union query, which includes the Memo field, returns all 806 characters.
    * I have a third, standard query, which I use to populate a report, that uses the Union query results. The third query truncates the Memo field to 255 characters. This third query contains no formatting or sorting.

    I have the latest version of Jet 4.0. I've checked everything I can think of. It looks like the Memo field is changed to a Text field, or formatting is applied to the Memo field, when I run the third query.

    Any thoughts?

  11. #11
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Memo Field Truncation (A2K SR1)

    Union queries apparently do some strange things. This Article (208926) in the MS Knowledge Base suggests that they routinely sort the data being returned even though you don't specify a sort. You might try adding the ALL clause to the SQL string and see if that changes the behavior.

    I also don't understand why you use the third "standard query" that you use to populate the report - why don't you simply use the Union query instead. As I indicated previously, we would probably use a series of append queries to create a temporary table - that approach seems to always work reliably.
    Wendell

  12. #12
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo Field Truncation (A2K SR1)

    Wendell,

    I'm sorry to bother you with this, but I appreciate your willingness to help. I've further narrowed down what's happening. Despite my previous messages, the problem has nothing to do with the Union query.

    I have a query that includes a Memo field. If I run the query WITHOUT Group By totaling, the query returns all characters in the Memo field. If I run the query WITH Group By totaling, the query returns only 255 characters in the Memo field. Unfortunately, I need Group By totaling. Does this make sense?

    Thanks again for your, and everybody's, help.

  13. #13
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Memo Field Truncation (A2K SR1)

    You aren't going to be able to group by and get the entire memo field as far as I know. It isn't possible to group on a memo field, so the query engine truncates it to get something it can handle.
    Charlotte

  14. #14
    3 Star Lounger
    Join Date
    Mar 2001
    Location
    Frederick, Maryland, USA
    Posts
    296
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Memo Field Truncation (A2K SR1)

    Thank you, Charlotte. That's what I was afraid of. Time for a work-around!

  15. #15
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Memo Field Truncation (A2K SR1)

    Sorry for the delay - I've been out of commission for a day and a half with a <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> <img src=/S/aflame.gif border=0 alt=aflame width=15 height=15> <img src=/S/hairout.gif border=0 alt=hairout width=31 height=23> problem with WinXP Home Edition and Direct Cable Connections that we finally had to solve with a back-up to floppy disk. Ain't new technology wonderful!

    Anyhow, Charlotte hit the nail on the head. The missing part of the puzzle was the group-by you were doing. Anytime you do a group by, Jet insists on sorting the field (which makes sense by the way), and in order to do that it has to truncate the field to 255 characters. A solution will probably require either creating some temporary tables, or doing yet another level of query where you can link to the memo field and not have to do a group by. Is it possible that further normalization (or denormalization) of the tables would make the problem more tractable?
    Wendell

Posting Permissions

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