Results 1 to 4 of 4
  1. #1
    btappan is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2013
    Posts
    43

    UNION ALL query still truncating data


    I have encountered a problem with a series of queries I use to combine product data from various fields to form a long text description. I have a query for each product type, and the descriptions in those resulting queries look fine. When I try to combine those queries using a union, the descriptions are truncated to 255 characters. I read a solution was to use UNION ALL instead of just UNION, but is still being truncated to 255.

    Tried:
    Code:
    SELECT  ID, pNAME, DescP1, DescP2
    FROM DESC_CB_BP_QB
    
    
    UNION ALL
    
    
    SELECT  ID, pNAME, DescP1, DescP2
    FROM DESC_FusBP_QB;
    Also tried:
    Code:
    SELECT *
    FROM DESC_CB_BP_QB
    
    
    UNION ALL
    
    
    SELECT  *
    FROM DESC_FusBP_QB;
    Last edited by btappan; 02-04-2020 at 01:01 PM.

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    Union All will include duplicates. Long text fields can be an issue. They will be truncated to 255 chars (text field constraint 255 limit) often.
    We need to know :
    -what exactly you are trying to accomplish in simple, plain English.
    -the table designs involved
    -the need and meaning of DescP1, DescP2

    For reference: (from w3schools)
    The UNION command combines the result set of two or more SELECT statements (only distinct values)
    The UNION ALL command combines the result set of two or more SELECT statements (allows duplicate values).

    Good luck with your project.

  3. #3
    btappan is offline Advanced Beginner
    Windows 7 64bit Access 2016
    Join Date
    Oct 2013
    Posts
    43
    I wound up solving it using Allen Browne's method here: http://allenbrowne.com/ser-63.html

    Where a "dummy" memo type filed is added in to the query to trick it, and it worked.

    I changed:

    Code:
    SELECT  ID, pNAME, DescP1, DescP2
    FROM DESC_CB_BP_QB
    
    
    
    
    UNION ALL
    
    
    
    
    SELECT  ID, pNAME, DescP1, DescP2
    FROM DESC_FusBP_QB;
    to:
    Code:
    SELECT  ID, pNAME, SellingNotes AS Desc
    FROM Product WHERE (False)
    
    
    
    
    UNION ALL
    
    
    
    
    SELECT  ID, pNAME, DescP1 & DescP2 AS Desc
    FROM DESC_CB_BP_QB
    
    
    
    
    UNION ALL
    
    
    
    
    SELECT  ID, pNAME, DescP1 & DescP2 AS Desc
    FROM DESC_FusBP_QB;

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 4
    Last Post: 08-09-2017, 12:06 PM
  2. Truncating data in Memo field
    By Chanda in forum Import/Export Data
    Replies: 1
    Last Post: 01-06-2015, 12:06 PM
  3. Loss of data with Union Query
    By Comiccradle in forum Queries
    Replies: 12
    Last Post: 05-26-2014, 09:41 AM
  4. memo field truncating data on form
    By gillianw in forum Forms
    Replies: 11
    Last Post: 06-04-2013, 06:24 AM
  5. Union query truncating a memo field
    By jpkeller55 in forum Queries
    Replies: 7
    Last Post: 05-27-2011, 02:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums