Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1093
  • Last Modified:

Query drops characters from MEMO field

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
query?


Tom
0
TomBock2004
Asked:
TomBock2004
  • 4
  • 2
  • 2
2 Solutions
 
Gustav BrockCIOCommented:
If wrapping the memo field in LTrim:

  MemoTxt: LTrim([YourMemo])

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

/gustav
0
 
kmslogicCommented:
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.

Kelly
0
 
TomBock2004Author Commented:
How do I go about processing it via VBA?

Tom
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
kmslogicCommented:
TomBock2004,

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
0
 
Gustav BrockCIOCommented:
First, the query picks up to 32k chars.

To extract the full memo use the function below.

/gustav


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
  Else
    ' Return empty string.
  End If
 
  LookupMemo = strMemo

Exit_LookupMemo:
  Exit Function
 
Err_LookupMemo:
  ' Return empty string.
  Resume Exit_LookupMemo
 
End Function
0
 
TomBock2004Author Commented:
Gustav:

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

Tom
0
 
TomBock2004Author Commented:
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?

Tom

P.S.  Raised points to 500
0
 
TomBock2004Author Commented:
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.

Tom
0

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now