[AccessD] Select Distinct not working on union query - due to MEMO field?

Hi Bill and JC

Right, 255 chars it is.

Further, using a second query you may even look up the full memo should you need it.
This I posted in 2003 but I haven't used it since so it is still not tested in versions later than A97:

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+.

/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>

/gustav


>>> jwcolby at colbyconsulting.com 12-07-2011 15:43 >>>
It never occurred to me to use left(MemoField,255) when I needed to do something like that.

The obvious is sometimes easy to miss.

John W. Colby
www.ColbyConsulting.com 

On 7/12/2011 8:51 AM, William Benson (VBACreations.Com) wrote:
> Oh that is good to know! Yes, that will cut out a step or two now and in
> future.
>
> I appreciate the follow up on this!
>
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com 
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
> Sent: Tuesday, July 12, 2011 8:02 AM
> To: accessd at databaseadvisors.com 
> Subject: Re: [AccessD] Select Distinct not working on union query- due to
> MEMO field?
>
> Hi Bill
>
> But you are missing the obvious - the DISTINCT is not needed if you use
> UNION and not UNION ALL.
>
> If you don't need memo fields, certainly don't use them. If you have the
> need (for holding text beyond 256 chars) you may in your UNION or DISTINCT
> query use:
>
>    LEFT(YourMemoField, 256)
>
> /gustav