sqlms-accessunionlongtext

How to keep LongText field value from getting trimmed?

I have an MS-Access database with two tables, both containing a LongText field, Description. I want to union these tables and get the result in one dataset. The problem is that uinon trims the field values to 255 characters.

This is my query:

SELECT Description, LenBefore, Len(Description) AS LenAfter FROM (
  SELECT Description, Len(Description) AS LenBefore FROM References
UNION
  SELECT Description, Len(Description) AS LenBefore FROM KeyWords
)

The result:

enter image description here

The MDB file is in 2002-2003 file format, and I'm running this in MS-Access 2016 64x.

Is there any way that I can get the full text in one single query?

Solution

  • Go into your query SQL view and change UNION to UNION ALL.

    In a UNION the result has to be de-duplicated, which means doing a comparison. Access only allows comparisons up to 255 characters, so trims the field. UNION ALL doesn't need de-duplication so this behaviour doesn't occur.