Query drops characters from MEMO field

Posted on 2005-04-21
Last Modified: 2012-06-21
I have a table to contains 2 memo fields (besides a bunch of text and number fields).

In one of the record's memo field, the "character count" is 555 characters (with spaces).

Now, once I created a query and inserted the 2 memo fields.  In the query, I lose some characters lots of text.
For instance the record that has 555 characters now shows only 255 characters in the query.

I am vaguely familiar about about the 256 max character concept.   However, that's why I chose "Memo" as data type for this field.  I was under the impression I would have an unlimited amount of characters to enter.  

Again, it's only the query that drops characters (not the table).  Why is that?  And how can I fix it in the

Question by:TomBock2004
    LVL 48

    Expert Comment

    by:Gustav Brock
    If wrapping the memo field in LTrim:

      MemoTxt: LTrim([YourMemo])

    doesn't help, you will have to use VBA to look up those memos.

    LVL 16

    Accepted Solution

    Hi TomBock2004,

    In the query you'll be limited to 255 characters for a text column.  You can do something like this to make multiple columns to hold your memo value:

    SELECT CStr([testmemo]) AS MyMemo, Mid([testmemo],1,255) AS MyMemo1, Mid([testmemo],256,255) AS MyMemo2, Mid([testmemo],512,255) AS MyMemo3
    FROM MemoTest;

    or you can process the memo fields in VBA as cactus suggests.


    Author Comment

    How do I go about processing it via VBA?

    LVL 16

    Expert Comment


    dim strQuery as striing
    dim strResult as string
    dim rs as DAO.recordset

    strQuery = "SELECT mymemofield from MyTable WHERE key='something'"
    set rs = currentdb.openrecordset(strQuery)
    strResult = rs!mymemofield

    ' strResult will contain the entire memo up to about 32k characters I believe
    LVL 48

    Assisted Solution

    by:Gustav Brock
    First, the query picks up to 32k chars.

    To extract the full memo use the function below.


    Public Function LookupMemo( _
      ByVal strSource As String, _
      ByVal strFieldID As String, _
      ByVal strFieldMemo As String, _
      ByVal lngID As Long, _
      ByVal varMemo As Variant) _
      As String

    ' Extracts without truncation to 32768 characters the
    ' content of a memo field in a query.
    ' Assumes proper wrapping of table/field names containing spaces
    ' like "[My field name]" and a single field unique numeric key.
    ' Typical usage (SQL):
    '   SELECT
    '     ID,
    '     LookupMemo("Table1", "ID", "MemoField", [ID], [MemoField]) AS FullMemo
    '   FROM
    '     Table1;
    ' 2003-12-29. Cactus Data ApS, CPH.

      ' Maximum length of string from memo field when retrieved in a query.
      Const clngStrLen  As Long = &H8000&

      Dim strExpr       As String
      Dim strDomain     As String
      Dim strCriteria   As String
      Dim strMemo       As String
      Dim lngLen        As Long
      On Error GoTo Exit_LookupMemo

      If Not IsNull(varMemo) Then
        lngLen = Len(varMemo)
        If lngLen < clngStrLen Then
          ' The memo field is not truncated.
          strMemo = varMemo
        ElseIf Len(strSource) > 0 And Len(strFieldID) > 0 And Len(strFieldMemo) > 0 Then
          ' The memo is probably truncated by the query.
          ' Lookup the full memo in strSource.
          strExpr = strFieldMemo
          strDomain = strSource
          strCriteria = strFieldID & " = " & lngID & ""
          strMemo = vbNullString & DLookup(strExpr, strDomain, strCriteria)
        End If
        ' Return empty string.
      End If
      LookupMemo = strMemo

      Exit Function
      ' Return empty string.
      Resume Exit_LookupMemo
    End Function

    Author Comment


    Am I supposed to put this in a module?    And then call it in the query via "LookupMemo"?


    Author Comment

    kmslogic & Gustav:

    Going back to the "drawing board"... the reason for something data is truncated is caused by something else.    Both of your version seemed to work fine in a testing query.    

    Then however, I plugged either suggestion into my actual query... this resulted in the same outcome.

    Here's what's going on:    If I create a basic query w/ only 2 fields (Autonumber and memo field), I show e.g. 100 records.   Now, in my actual query I have included other fields that require me to select "Group By".  

    Once I apply the GroupBy in the query, I still show the 100 records (since I dropped the fields for testing purposes), but now the text in the memo field is truncated.    So, again "Group By" (no matter what approach I'm using) truncates the data for each record.

    Do you know of another workaround that will allow me to use GroupBy in the query?


    P.S.  Raised points to 500

    Author Comment

    I figured it out...

    Although I'm using GroupBy, I now changed it to "First" for the Memo field.   Now data is not being truncated.

    Thanks anyhow.   Your other methods are still worth some points to me.


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
    Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

    732 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now