473,320 Members | 2,083 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Select query truncates memo field

Hi all,

I have a query in ASP.NET that selects text from a memo field in Access
(among other things). The query appears to be truncating the text at
255 characters. I've done a little research on this and found some
other Google Groups threads that were helpful, but I still don't have a
solution. My query is rather complicated and uses a union, but not the
keyword distinct. The query is displayed in a datagrid, and I don't
know how I would be able to break it up to solve the 255 problem and
still have it display in the one datagrid. Is there anything I can add
to it? Here's the query:

(Select pk_characteristic_id, vc_characteristic_name,
pk_description_id, tx_description From ((tb_descriptions d inner join
tb_agents a on d.fk_agent_id = a.pk_agent_id) inner join
tb_characteristics c on d.fk_characteristic_id =
c.pk_characteristic_id) where d.fk_agent_id = @agentID) UNION (select
pk_characteristic_id, vc_characteristic_name, 0 AS pk_description_id,
'' from tb_characteristics where pk_characteristic_id NOT IN (select
fk_characteristic_id from tb_descriptions where fk_agent_id =
@agentID))

Is there a solution to my problem, or am I going to have to switch
databases? Please help!

Thanks!
Anna

Nov 13 '05 #1
4 4073
Anna,
I don't see a Group By in your query, but this error is usually because
access can't Goup By memo fields. Is it possible you are Gouping
somthing further down the line? Since you are using access as a
backend, rather than fail, Access kindly converts the memo to text(255)
and runs the qry. You have probably done this, but sometimes opening
up Access and pasting the sql into a qry will give you error messages
that you didn't get using it as a backend - to help you troubleshoot.
HTH
Pachydermitis

Nov 13 '05 #2
Thanks for the quick reply. Unfortunately, running the query in Access
doesn't produce any errors, and it truncates the memo fields just like
it does in the code. Is this a bug in Access?

Thanks,
Anna

Nov 13 '05 #3
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Your query looks like an SQL Server syntax - you have the parameter
@agentID. Access queries do not use parameters like that. In the
second UNION you have a place holder of '' for the tx_description
column. I believe this is the culprit - it is indicating that the data
type of that place holder is a VARCHAR, which, for some reason, is
truncating to 255 chars. If your data back-end really is SQL Server you
might try CAST('' to TEXT) which will set the data type to TEXT (Memo in
SQL Server).

Did you test each query in the UNION query, one at a time, to see if
they both return the correct data? IOW, if the top query returns the
TEXT/Memo data correctly, but adding the bottom query truncates the
data, then you know the bottom query is the culprit.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQla88IechKqOuFEgEQK7nwCfavBByOKu7HCNkafi7ctDJT F3B9oAnjCk
YntwDg5QPrN0HqLT3RLGbYPp
=tC12
-----END PGP SIGNATURE-----

Anna wrote:
Hi all,

I have a query in ASP.NET that selects text from a memo field in Access
(among other things). The query appears to be truncating the text at
255 characters. I've done a little research on this and found some
other Google Groups threads that were helpful, but I still don't have a
solution. My query is rather complicated and uses a union, but not the
keyword distinct. The query is displayed in a datagrid, and I don't
know how I would be able to break it up to solve the 255 problem and
still have it display in the one datagrid. Is there anything I can add
to it? Here's the query:

(Select pk_characteristic_id, vc_characteristic_name,
pk_description_id, tx_description From ((tb_descriptions d inner join
tb_agents a on d.fk_agent_id = a.pk_agent_id) inner join
tb_characteristics c on d.fk_characteristic_id =
c.pk_characteristic_id) where d.fk_agent_id = @agentID) UNION (select
pk_characteristic_id, vc_characteristic_name, 0 AS pk_description_id,
'' from tb_characteristics where pk_characteristic_id NOT IN (select
fk_characteristic_id from tb_descriptions where fk_agent_id =
@agentID))

Is there a solution to my problem, or am I going to have to switch
databases? Please help!

Nov 13 '05 #4
Thank you - the problem was the union. By the way, I am using Access as
the backend - the "@agentID" is just .NET's syntax for passing in the
variable. Anyways, once I realized the problem was the union I was able
to do a little more research and came across this:

http://groups-beta.google.com/group/...2837e03849daa4

All I had to do was change "union" to "union all" - a nice simple
solution, the way I like it!

Thanks,
Anna

Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: GorDon | last post by:
Hi, I have a report based on a query. The query grabs a memo field from my main table, yet when I display the memo field in the report it truncates the memo field (the field needs to hold more...
8
by: skinnybloke | last post by:
Hi - I have a problem with a memo field being truncated to about 255 characters when running a Access 2002 query. This only seems to happen if I use SELECT DISTINCT. It works ok using SELECT by...
2
by: Bob Dydd | last post by:
Hi Everybody I have a Listbox that has a Memo field attached to it's 2nd column which on double click copies the memo field to another Memo Field. Forms!! = ListClassification.Column(1) My...
3
by: shripaldalal | last post by:
hi all, i have the following fields.... Document_Name, Document_No, Document_Date, Order_No, Order_Date, Peripheral The first 5 you can understand, peripheral is a memo field which...
2
by: jacoballen | last post by:
I have a query that combines the results of three related tables. The memo fields are truncated to 255 characters, but I need all the information in them. I'm aware that removing code such as...
2
by: Keith Wilby | last post by:
I have a table containing different field types including a memo field. If I do a select distinct on the table the memo field is truncated to 256 characters. I've never encountered this before,...
2
by: Genalube | last post by:
I am running a query that includes the inclusion of memo fields, my query pulls from four different tables with one to many relationships between them. The relationships are such that when I run my...
2
by: cherylwalsh35 | last post by:
I am working in access - using a select Query. I am including a memo field. The table has all of the data in the memo field displayed - however when I run a select query and include the memo...
5
Shakss2
by: Shakss2 | last post by:
Hi... I am exporting some data from a table with a memo field to excel but the excel report truncates the memo field data to 255 chars. Dim strPath As String ' Current path strPath =...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.