Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Posts
    2

    Unanswered: union query help needed

    I've created a simple union query to combine the results of two other queries - the main data from which is contained in "memo" Access fields. My problem is that those fields are limited to 255 characters in the union query - they're fine (as memo fields) in the two "uncombined" queries. Is there any SQL code anyone is aware of to list the entire contents in a union query?

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Not sure what you mean, I just wrote a union query on memo fields and the data was present.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Feb 2004
    Posts
    2
    my data is present - it's just truncated at 255 characters. What version of Access are you using?

  4. #4
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    Okay, I've done some reading and testing with recordsets and found that the issue of truncation comes up because of Access' inability to handle memo fields during a comparison (c.f.: http://dbforums.com/arch/201/2003/1/656871). Union queries need to do a comparison to ensure that you have the same number of columns in your query so that's probably where the truncation happens. The data types don't need to be compared because you can union dates with text, but they probably need to be put into a comparable form to count the columns.

    This problem will also occur with a group by and with a make table.

    You may need a temporary table with the memo field already defined, and append the data from your two separate queries. If the data design is there, you can ensure you won't get duplicates within the table (append, then append where the unique identifiers don't exist in the table already).
    All code ADO/ADOX unless otherwise specified.
    Mike.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •