By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,572 Members | 946 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,572 IT Pros & Developers. It's quick & easy.

Data truncated in MS Access UNION query

P: 5
Hi All,

I'm using ODBC to link my Access 2000 database tables to MS SQL Server 2000 database and create queries and reports in Access. When I use a single query to retrieve a text field with large data, all data in the field are retrieved. However, when I use the same query in a UNION query, the data of the same field are truncated ( may be 255 characters but I didn't actually count ), but this seems to happen on certain records only.

Please help. Thanks
Feb 7 '07 #1
Share this Question
Share on Google+
6 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi All,

I'm using ODBC to link my Access 2000 database tables to MS SQL Server 2000 database and create queries and reports in Access. When I use a single query to retrieve a text field with large data, all data in the field are retrieved. However, when I use the same query in a UNION query, the data of the same field are truncated ( may be 255 characters but I didn't actually count ), but this seems to happen on certain records only.

Please help. Thanks
The short answer to this is Access Memo fields don't preform well in Union queries.

Have a look at this tutorial.

Mary
Feb 7 '07 #2

P: 5
Thanks, Mary.

The field concerned is a text field of size for 5000 characters in a MS SQL Server 2005 database. My MS Access is actually Access 2002. Does the UNION problem happen to large text field as well ?

The question is, the same set of data works fine in the same environment when retrieved by a single query. When I modified the query to a UNION query with the two queries a replica of the original one that works and the difference of the two queries is the WHERE parts. The puzzle is that happens to certain records only but other records with large text data are OK.

Still scratching my head ....


The short answer to this is Access Memo fields don't preform well in Union queries.

Have a look at this tutorial.

Mary
Feb 7 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks, Mary.

The field concerned is a text field of size for 5000 characters in a MS SQL Server 2005 database. My MS Access is actually Access 2002. Does the UNION problem happen to large text field as well ?

The question is, the same set of data works fine in the same environment when retrieved by a single query. When I modified the query to a UNION query with the two queries a replica of the original one that works and the difference of the two queries is the WHERE parts. The puzzle is that happens to certain records only but other records with large text data are OK.

Still scratching my head ....
The large text field in SQL server is translated by Access as a memo field. Access only has two options Text field (max char size of 255) and Memo field. As the tutorial says anything outside of a basic query on this field just doesn't really work in access.

Mary
Feb 7 '07 #4

P: 5
Thanks again, Mary.

I now understand the UNION problem and I'll avoid using this.

I have actually changed my query back to a single query by using a IIF function to condition the outcome of two DateDiff functions and that works fine. Processing time improves as well as there is only one pass of the dataset now.

Did Microsoft fix this known UNION problem in the latest Access release ?

Thanks,

RLau


The large text field in SQL server is translated by Access as a memo field. Access only has two options Text field (max char size of 255) and Memo field. As the tutorial says anything outside of a basic query on this field just doesn't really work in access.

Mary
Feb 7 '07 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Thanks again, Mary.

I now understand the UNION problem and I'll avoid using this.

I have actually changed my query back to a single query by using a IIF function to condition the outcome of two DateDiff functions and that works fine. Processing time improves as well as there is only one pass of the dataset now.

Did Microsoft fix this known UNION problem in the latest Access release ?

Thanks,

RLau
The problem is really in the use of Memo fields as it is not just UNION queries that are affected. To the best of my knowledge M$ has made no effort to address this. It just doesn't handle text fields above 255 characters in any managable way.

Mary
Feb 7 '07 #6

NeoPa
Expert Mod 15k+
P: 31,485
Thanks again, Mary.

I now understand the UNION problem and I'll avoid using this.

I have actually changed my query back to a single query by using a IIF function to condition the outcome of two DateDiff functions and that works fine. Processing time improves as well as there is only one pass of the dataset now.

Did Microsoft fix this known UNION problem in the latest Access release ?

Thanks,

RLau
They would see it as more of a feature than a problem.
Using Memo type fields in these circumstances (assuming it were supported) would be such a resource and processor overhead that implementing it is probably not such a great idea.
No one (IMHO) should be thinking of using text fields of that size anywhere that needs any processing on them (Sorting, Filtering etc - UNION has an implicit sort) except in extremis. Access simply forces a better design on the developer.
Feb 8 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.