Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2006
    Posts
    27

    Unanswered: Memo field data cut off.

    I have problems with memo fields truncating in the table that were inserted via straight insert queries. I even had one instance where an error message was included in the memo field. It mentioned that there may be a memory problem. Which may be a valid point since I created a one-form, one-table database and ran insert and update queries for the memo field and had noproblems with 1000+ character entries. Binding the text control is a solution to the problem, but not a viable option. I'd appreciate any help provided.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Am I understanding you correctly, the text box you're using to enter the data that you then insert into the table is not bound to the table?
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Aug 2006
    Posts
    27
    I have an insert statement that inserts values from a few text box controls to the table. The text that ends up in the memo field in the table gets cut off. And, yes, the textboxes are not bound.

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    As I understand it, that's your problem. There is, as you know, nomemo type of control on Access forms, only text boxes. When the text box is not bound to an underlying memo field, Access has no way of knowing that the text box is being used for anything other than a text field, and thus limits it to 255 characters! The only work around that comes to mind would be to possibly bind the text box to a memo field in a separate table, letting Access know that it is being used for a memo field, and then inserting it as you are now trying to do in the target table.

    Even if it were bound to the target table, Access is still very particular in how it works with memo fields thru queries! Here's some note to myself I had stashed away on the subject. Don't remember the source(s).

    Crosstab queries, summary queries, Union queries, and Queries that use Distinct or DistinctRow will all truncate a memo field to 255 characters so Access can perform the required functionality of eliminating duplicates. Also, if you have specified a format in the field's Format property, I believe that will truncate the data that is displayed. If Unique Value Property is set to Yes, Access has to compare the values and therefore Memo Field values are truncated.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Missinglinq
    As I understand it, that's your problem. There is, as you know, nomemo type of control on Access forms, only text boxes. When the text box is not bound to an underlying memo field, Access has no way of knowing that the text box is being used for anything other than a text field, and thus limits it to 255 characters! The only work around that comes to mind would be to possibly bind the text box to a memo field in a separate table, letting Access know that it is being used for a memo field, and then inserting it as you are now trying to do in the target table.

    Even if it were bound to the target table, Access is still very particular in how it works with memo fields thru queries! Here's some note to myself I had stashed away on the subject. Don't remember the source(s).
    Unbound textboxes allow up to a Memo max of characters also (been there do that) ... The most likely culprit is the query truncating as noted. Stored queries are notorious for having hard limits on the # of characters contained within the query. That is why when I've ever dealt with memos, I use a recordset and poke the memo accross using the AppendChunk method ...
    Back to Access ... ADO is not the way to go for speed ...

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    If you do an insert command via vba, you may want to define the memo field as a variant and then insert the variant name. I had problems inserting in vba a memo field and it cutting off but when I did something like this:

    dim MM as variant
    MM = Forms!MyForm!MyMemoField
    .....do insert command here....

    it worked without cutting off the text.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

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