MS Access - SQL query - NVARCHAR(MAX) is cut off when using DISTINCT keyword in LEFT JOIN

Question

I have a SQL Server connected to an Access frontend. Some text fields are set to NVARCHAR(MAX) and are working fine when I use direct table access to them.

For a query I needeed a LEFT JOIN to get access to a WHERE clause on fields of the other table so I created something like this:

strSQL = "SELECT DISTINCT dbo_tbl_Changes.Change_Nr, dbo_tbl_Changes.Title, dbo_tbl_Changes.ChangeContent, dbo_tbl_Changes.Date
FROM dbo_tbl_Changes LEFT JOIN dbo_tbl_Parts ON dbo_tbl_Changes.Change_Nr = dbo_tbl_Parts.Change_Nr
WHERE dbo_tbl_Parts.PartType = 'bolt'
ORDER BY dbo_tbl_Changes.Date DESC "

Forms![frm_ChangeOverview].RecordSource = strSQL
Forms![frm_ChangeOverview].Requery

With this, the NVARCHAR(MAX) field ChangeContent is getting cut off and not showing all its content.

If I leave out the DISTINCT keyword, the NVARCHAR(MAX) is correctly working within the form in a RichText field. But then I will get more results than I wawnt (doubled entries) as I need the DISTINCT keyword in that query to remove those doubled (or even more) results.

One example: The ChangeContent has 1441 characters and is truncated to 157 characters but only when I use the DISTINCT keyword.

I'm using the native SQL ODBC driver as no other can be installed in my environment.

What could be the issue?

It looks like you have the same issue as here: https://stackoverflow.com/questions/4833549/nvarcharmax-still-being-truncated. If not, please describe it in a little more detail. — Jesusbrother, May 28 '22 at 09:16
@Jesusbrother this could be similar, whereas the truncated entries do have much less than those 4000 characters. And as I'm not writhing a dynamic query whthin the SQL server, I cannot figure out how to use this "CAST" statement in my case to try it out. — karlo922, May 28 '22 at 09:23
You could also try to use a **Pass-Through query** as record source for your form (and edit it's SQL on the fly). That won't have this problem. It will be read-only, but the current one will be too (I think). — Andre, May 28 '22 at 15:13
@Andre: But how to do this? I cannot find a good example how to "dynamically" change the RecordSource of my form when using a pass-through query. How to set something like Forms![frm_ChangeOverview].RecordSource = PassThorugh Query? — karlo922, May 28 '22 at 15:55
I found it out now - using the Pass-Through query was a good idea! — karlo922, May 28 '22 at 16:49

score 2 · Answer 1 · answered May 28 '22 at 11:06

This is a known issue and is because Access reads the field as a Long Text (memo) field. However, Access can only apply Distinct on 255 characters or less.

You may have to look up the full record afterwards, for example from this old example:

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

Thank you very much, but how can I integrate this in my query? If I use it it will give me the text result of the one ID but how to include this in my query so can use this as a RecordSource? I edited my question to show how I use it. — karlo922, May 28 '22 at 13:49
That depends. As shown in the in-line comment, but you have no unique ID, so you may change from `Distinct` to `Group By` and then use `First([ID])` or similar to pick a record to display. — Gustav, May 28 '22 at 14:01
I did not find it out - what do you mean with "it depends"? As soon as I use "DISTINCT" again with the function, it will again truncate the data even in the ChangeContentMemo field which I created with your function example. — karlo922, May 28 '22 at 14:59
And when trying to use "Group by" it gives me an error that the LookupMemo function is not part of an aggregate function — karlo922, May 28 '22 at 15:10
_Depends_ means that I don't have your data, neither the desired output. You may need to use a second query using the one you have as source and then implement the `LookupMemo` in the second query. — Gustav, May 28 '22 at 16:29
I now solved it by using a pass-through query like @Andre suggested. — karlo922, May 28 '22 at 16:50

MS Access - SQL query - NVARCHAR(MAX) is cut off when using DISTINCT keyword in LEFT JOIN

1 Answers1