mdstalla
asked on
Access Query truncates Memo Field
I have an Access Query partially made up of Memo Fields pulled from a Table. My Query is set up as 'Select Top 1,' so that it only displays the first record-set.
Access truncates the data within the Memo Fields—cutting them off at 255 characters.
Does anyone know how to prevent this from happening?
Access truncates the data within the Memo Fields—cutting them off at 255 characters.
Does anyone know how to prevent this from happening?
You might have a simpler query if you used a DLookup() function instead of a subquery.
Example:
Example:
SELECT Top 1 ID, Field1, Field2, Field3,
DLookup("memofld1", "MyTable", "ID=" & MyTable.ID) As MF1,
DLookup("memofld2", "MyTable", "ID=" & MyTable.ID) As MF2,
DLookup("memofld3", "MyTable", "ID=" & MyTable.ID) As MF3
FROM MyTable
Where (some row criteria goes here)
This is a known issue.
You can use this function:
You can use this function:
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
/gustav
ASKER
Hi Gustav:
Regarding the code you provided, where do I put this?
Regarding the code you provided, where do I put this?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Yup, that seemed to work. Thanks again for all of your help G!
You are welcome!
/gustav
/gustav
SELECT sq.*, T.MemoField
FROM (
SELECT Top 1 ID, Field1, Field2, Field3
FROM YourTable
ORDER BY SomeField) as sq
INNER JOIN yourTable as T
ON sq.ID = T.ID