locked
UNION query not providing full results in memo field RRS feed

  • Question

  • The following query works; however, I recently noticed that the Note Field does not provide the full contents of the field in the resulting set when I know the fields has more.  It seems that it is cutting it off short.
    SELECT [qryTracking-Task].[Project], [qryTracking-Task].[TrackingDate], [qryTracking-Task].[Title], PlainText([qryTracking-Task].[TrackingNote]) AS Notes FROM [qryTracking-Task]
    UNION SELECT ALL [qryTracking-SubTasks].[Project], [qryTracking-SubTasks].[TrackingDate], [qryTracking-SubTasks].[Title], PlainText([qryTracking-SubTasks].[TrackingNote]) AS Notes FROM [qryTracking-SubTasks];

    Can anyone assist me and correcting this issue.
    Wednesday, March 29, 2017 3:54 PM

Answers

  • even with your recommendation, it still truncates the memo field.  Any recommendations?

    You may have to exclude the Memo field from the UNION query and just add it later with a separate query.

    Just a thought...

    Wednesday, March 29, 2017 8:13 PM

All replies

  • Hi,

    Unfortunately, this is "normal" (expected) behavior. See Allen's website for an explanation.

    Hope it helps...

    • Proposed as answer by Chenchen Li Tuesday, April 4, 2017 8:21 AM
    Wednesday, March 29, 2017 3:58 PM
  • Unfortunately, this is "normal" (expected) behavior. See Allen's website for an explanation.

    In case you take too quick a look and don't read that web page closely, note that you can avoid the truncation by specifying "UNION ALL" instead of just "UNION" in your query, so long as you don't need the query to filter out duplicate values.


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Proposed as answer by Chenchen Li Tuesday, April 4, 2017 8:21 AM
    Wednesday, March 29, 2017 6:09 PM
  • even with your recommendation, it still truncates the memo field.  Any recommendations?
    Wednesday, March 29, 2017 8:08 PM
  • even with your recommendation, it still truncates the memo field.  Any recommendations?

    You may have to exclude the Memo field from the UNION query and just add it later with a separate query.

    Just a thought...

    Wednesday, March 29, 2017 8:13 PM
  • even with your recommendation, it still truncates the memo field.  Any recommendations?

    Hmm.  It seems to be related to the PlainText function, which I hadn't noticed before.  Here's a way to get what you want, I think:

    SELECT T.[Project], T.[TrackingDate], T.[Title], PlainText(T.Notes) FROM
    (
        SELECT [Project], [TrackingDate], [Title], [TrackingNote] AS Notes FROM [qryTracking-Task]
        UNION ALL
        SELECT [Project], [TrackingDate], [Title], [TrackingNote] AS Notes FROM [qryTracking-SubTasks]
    ) As T


    Dirk Goldgar, MS Access MVP
    Access tips: www.datagnostics.com/tips.html

    • Proposed as answer by Chenchen Li Tuesday, April 4, 2017 8:21 AM
    Wednesday, March 29, 2017 8:28 PM
  • I ended up doing just that.  Thanks for the help everyone.
    Wednesday, April 5, 2017 12:30 PM
  • I ended up doing just that.  Thanks for the help everyone.

    Hi,

    You're welcome. Dirk and I were happy to assist. Good luck with your project.

    Wednesday, April 5, 2017 3:02 PM