alexealden
asked on
Access 2007 how so I stop a union query from truncating data over 255 characters
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:
https://www.experts-exchange.com/questions/21236557/Union-query-truncating-memo-field.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
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:
https://www.experts-exchange.com/questions/21236557/Union-query-truncating-memo-field.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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
You are welcome!
/gustav
/gustav
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!
SELECT * FROM SomeTable
UNION ALL SELECT * From SomeOtherTable