[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access 2007 how so I stop a union query from truncating data over 255 characters

Posted on 2009-04-15
5
Medium Priority
?
378 Views
Last Modified: 2012-06-27
I have 2 queries that I have created a union query out of. The 2 x orgininal queries work well and does not truncate the slightly longer than 255 charachters description column

The first qeury looks like this:

SELECT "##" AS exprRM, [products]![Product Code] & "-" & [Part Number Choice] AS [Product Code], Products.[Short Description], [Description] & "<ul> <li>" & [bullet point 1] & "</li> <li>" & [bullet point 2] & "</li> <li>" & [bullet point 3] & "</li> <li>" & [bullet point 4] & "</li> <li>" & [capacity x 2] & "</li> </ul>" AS [desc], [Evolve Components : Inserts].[Insert type], Null AS exprPV2, Null AS exprPV3, Null AS exprPV4, Null AS exprRT, Null AS exprRT1, Products.Brand, Products.Model, Null AS exprCLASS, Products.Image, Null AS exprTHUMB, Products.image2, Products.image3, "55" AS exprGT, "3.43" AS exprGWRAP, [Search Term 1] & " " & [Search Term 2] & " " & [Search Term 3] & " " & [Search Term 4] & " " & [Search Term 5] AS exprSEARCH, "G" AS exprVATCODE, [Evolve Contract Price - Calculation].[50], Null AS ExprSpecialPrice, Null AS ExprSpecialPriceStart, Null AS ExprSpecialPriceEnd, "0" AS exprAVAIL, Products.[Stock Level], "3" AS exprSTOCKCONTROL, "12" AS exprEXPORTABLE, Products.Weight, Null AS exprunused1, Null AS exprunused2, Null AS exprunused3, Null AS exprunused4, Null AS exprunused5, Null AS exprunused6, Null AS exprunused7, Null AS exprunused8, Null AS exprunused9, Null AS exprunused10, Null AS exprunused11, Null AS exprunused12, "##" AS exprEOR
FROM ([Evolve Components : Inserts] INNER JOIN Products ON [Evolve Components : Inserts].[Insert Size] = Products.[Album Inserts]) INNER JOIN [Evolve Contract Price - Calculation] ON Products.[Product Code] = [Evolve Contract Price - Calculation].[Product Code]
WHERE (((Products.[Amazon / Edirectory])=Yes));

The second query looks like this:

SELECT "##" AS exprRM, Products.[Product Code], Products.[Short Description], [Description] & "<ul> <li>" & [bullet point 1] & "</li> <li>" & [bullet point 2] & "</li> <li>" & [bullet point 3] & "</li> <li>" & [bullet point 4] & "</li>" & "</li> </ul>" AS [desc], "30 x White Classic Style Pages" AS exprPV1, Null AS exprPV2, Null AS exprPV3, Null AS exprPV4, Null AS exprRT, Null AS exprRT1, Products.Brand, Products.Model, Null AS exprCLASS, Products.Image, Null AS exprTHUMB, Products.image2, Products.image3, "55" AS exprGT, "3.43" AS exprGWRAP, [Search Term 1] & " " & [Search Term 2] & " " & [Search Term 3] & " " & [Search Term 4] & " " & [Search Term 5] AS exprSEARCH, "G" AS exprVATCODE, [Evolve Contract Price - Calculation].[50], Null AS ExprSpecialPrice, Null AS ExprSpecialPriceStart, Null AS ExprSpecialPriceEnd, "0" AS exprAVAIL, Products.[Stock Level], "3" AS exprSTOCKCONTROL, "12" AS exprEXPORTABLE, Products.Weight, Null AS exprunused1, Null AS exprunused2, Null AS exprunused3, Null AS exprunused4, Null AS exprunused5, Null AS exprunused6, Null AS exprunused7, Null AS exprunused8, Null AS exprunused9, Null AS exprunused10, Null AS exprunused11, Null AS exprunused12, "##" AS exprEOR
FROM Products INNER JOIN [Evolve Contract Price - Calculation] ON Products.[Product Code] = [Evolve Contract Price - Calculation].[Product Code]
WHERE (((Products.[Amazon / Edirectory])=Yes));

the union query looks like this:

SELECT [Edirectory UK Feed Flat File - Parents].*
FROM [Edirectory UK Feed Flat File - Parents];
UNION ALL SELECT [Edirectory UK Feed Flat File - Children].*
FROM [Edirectory UK Feed Flat File - Children];

trouble is when I run the union query the desc column is then truncated although as I say it works beatifully in its own query.

[Description] & "<ul> <li>" & [bullet point 1] & "</li> <li>" & [bullet point 2] & "</li> <li>" & [bullet point 3] & "</li> <li>" & [bullet point 4] & "</li>" & "</li> </ul>" AS [desc]

This article here appears to offer the solution:

http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_21236557.html

But I am afraid I am having trouble figuring out how to implement the advice. Could someone elaborate a little on this solution. Thanks.

Alex

0
Comment
Question by:alexealden
  • 2
  • 2
5 Comments
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24150304
Are you using a UNION ALL when creating the union SQL statement?

SELECT * FROM SomeTable
UNION ALL SELECT * From SomeOtherTable
0
 
LVL 52

Accepted Solution

by:
Gustav Brock earned 2000 total points
ID: 24150580
Union queries always return strings.

To retrieve the memo, at least one method exists where you create two queries like:

    SELECT
        1 AS TableID,
        ID
    FROM Table1;

    SELECT
        2 AS TableID,
        ID
    FROM Table2;

Then union these:

    SELECT *
    FROM Query1
    UNION
    SELECT *
    FROM Query2;

Finally create a query selecting * from the union query and make outer
joins (using TableID and ID) between this and the two base tables.
The memo fields can then be concatenated as one of them - in the query -
always will be Null:

    UnionMemo: Table1!Memo & Table2!Memo

/gustav

0
 

Author Closing Comment

by:alexealden
ID: 31570574
Thanks, I figured this out as it happens but this was exactly what I did. I did a very simple union query with just the primary field and then created a new query in design view and I was off! Thanks for your help.
0
 
LVL 52

Expert Comment

by:Gustav Brock
ID: 24151362
You are welcome!

/gustav
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 24173923
HAH! ... I should have read more carefully ... the UNION ALL is right in the original post! ... <dazed and confused> ... no wonder I can't find the mustard in the refridgerator .... LOL!
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question