asked on

Access Query Cutting off Memo Text

I have a Query that is organized by “Select Distinct Top 1”

Access is Ternicating (sp?) the records displayed in the Query and cutting the text off at 255 characters.

I created a Module with the following code—but it doesn’t seem to be working.

Am I missing something here? Can anyone think of what I’m doing wrong?

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

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

strMemo = varMemo
ElseIf Len(strSource) > 0 And Len(strFieldID) > 0 And Len(strFieldMemo) > 0 Then

strExpr = strFieldMemo
strDomain = strSource
strCriteria = strFieldID & " = " & lngID & ""
strMemo = vbNullString & DLookup(strExpr, strDomain, strCriteria)
End If
Else

End If

LookupMemo = strMemo

Exit_LookupMemo:
Exit Function

Err_LookupMemo:

Resume Exit_LookupMemo

End Function

Nick67

Am I missing something here? Can anyone think of what I’m doing wrong?
Memo text is evil.
There are many, many ways and times that it gets truncated, and that is an expected result.
http://allenbrowne.com/ser-63.html
DLookup(strExpr, strDomain, strCriteria)
is one of them.

What are you looking to accomplish?

mdstalla

ASKER

I just want to be able to keep the Select Top 1 ordering-- but not have my memo fields get limited to 255 Characters.

ASKER CERTIFIED SOLUTION

Nick67

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Nick67

Recordset code is less likely to truncate
You can try this

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

   Dim db as Database
   Set rs = CurrentDb.OpenRecordset("select " & strFieldMemo & " from " & strSource & " where " &  strFieldID & " = " & lngID & "", dbOpenDynaSet)
   if rs.RecordCount <> 0 then  
       LookupMemo = rs.fields(strExpr)
   Else
       LookupMemo = ""
   End If
   rs.close
   Set rs = nothing
 End Function

Open in new window

mdstalla

ASKER

Yah, Nick-- It's strange but the Parent Query doesn't cut the Memo-record off at 255 characters as long as the record comes from a Child Query rather than direct from a Table. Very strange.

Anyway, your trick worked. Thanks for the tip.

Nick67

If you remember "Memo text is evil" you can google up most of the ways to work around it.
Or the times when you are going to be SOL
Like putting memo text in Excel cells, where 255 will display in the cell, 1024 in the formula bar, and the rest will get truncated.

Glad to be of service