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

re: Select query truncates memo field

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

re: Select query truncates memo field

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

re: Select query truncates memo field

-----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:[color=blue]
> 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![/color]

re: Select query truncates memo field

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