query truncates long text field!

[cherold]

I have a query that gets all values from a table conditional on a value in another table. The query returns a truncated "long text" field; it only shows the first 255 characters. If I just open the table directly it displays the full text.



This is my query:

SELECT DISTINCT Compositions.* 
FROM Discs INNER JOIN (Compositions INNER JOIN DiscContents ON Compositions.CompositionID = DiscContents.composition_id) ON Discs.DiscID = DiscContents.disc_id
WHERE (((Discs.Status)<12));

The field that's giving me trouble is Compositions.Composition. It is defined as "long text." I found one thread on a similar issue (which turned out to be an issue with an import from Excel, which is not my case) in which one suggestion was to change the text format to "rich text." (Although apparently for that to work you'd need to set it to rich text before importing from Excel so that's irrelevant.) So I'll mention that changing the field to rich text did not fix the issue.

Why isn't it giving me the full field contents? How do I fix it?

[CJ_London]

It’s because you are grouping remove the distinct and you should see the full text

[cherold]

I added the DISTINCT because without it the query gives me doubles of Composition rows that are referenced more than once in DiscContents. So I can't remove it unless there's an alternate way to prevent those doubles.

Why does DISTINCT affect the length of returned text fields? Why would grouping interfere with how fields are displayed?

It’s because you are grouping remove the distinct and you should see the full text

[CJ_London]

There is a limit to how many chars can be grouped for any one field - 255. Implication is your tables are not constructed correctly or you query needs to be constructed differently. Only on my phone so can’t really advise

[Micron]

Operations that involve long text fields (aka memo) often truncate data; that's a known "thing". You might solve this by first preventing dupes in query1 (but do not include long text field) then include memo field in 2nd query and join on the field that you forced to be unique.

[isladogs]

Allen Browne has an excellent summary of the reasons why memo/long text fields can get truncated in Access http://allenbrowne.com/ser-63.html
Basically any processing will cause truncation.

[esoenke]

Thanks-- this just saved me endless trouble. Much appreciated!