[AccessD] Query truncates memo field. Solved

Brilliant! :-) Gustav

Jim

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: Monday, December 29, 2003 6:58 AM
To: Access Developers discussion and problem solving
Subject: [AccessD] Query truncates memo field. Solved


Hi all

Someone (JC?) mentioned this issue recently and I have experienced it
too for Access 97 - even if you directly can fill a memo field to 64
K, a query will only retrieve 32 K.

Even worse, if you fill a memo field with, say, the Rich Text Box
control you can reach megabyte field content sizes while still only
the first 32 K are retrieved.

To circumvent this you may use DLookup to fetch the full content. Now,
when the content is smaller than 32 K it is waste of time to pass
DLookup; thus DLookup should only be used when necessary.

Here is a method to do that. It is not tested in Access 2000+.

Happy New Year!

/gustav

<code>

Public Function LookupMemo( _
  ByVal strSource As String, _
  ByVal strFieldID As String, _
  ByVal strFieldMemo As String, _
  ByRef lngID As Long, _
  ByRef 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

</code>

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com