Linked Sybase tables & "Distinct" keyword

[CP]

I'm hoping that someone can figure our WHY this is happening:
I have an Access 2003 application that uses Linked Sybase tables.
When I create a query like this:

SELECT Tta.TTA_IFMS_ID, Tta.INTRIGHTCOM, Tta.INTFMVCOM,
Tracts.TRACTS_IFMS_ID
FROM Tta INNER JOIN Tracts ON Tta.TRACTS_IFMS_ID =
Tracts.TRACTS_IFMS_ID
WHERE (((Tta.TTA_IFMS_ID)="600226409725877"));

the results for the Tta.INTRIGHTCOM memo field are correct and looks
like this:

Deed-out of a portion of the Wells 1 tract including mineral rights to
the State of Arizona, Game and Fish Commission (AZFG). TNC's access
easement previously acquired from Wells was also conveyed with the
property. TNC reserved the right to use the access easement to
provide access to all lands in the vicinity in which TNC has a fee and/
or conservation easement interest

However, when I add DISTINCT to the query,
SELECT DISTINCT Tta.TTA_IFMS_ID, Tta.INTRIGHTCOM, Tta.INTFMVCOM,
Tracts.TRACTS_IFMS_ID
FROM Tta INNER JOIN Tracts ON Tta.TRACTS_IFMS_ID =
Tracts.TRACTS_IFMS_ID
WHERE (((Tta.TTA_IFMS_ID)="600226409725877"));

the results for the Tta.INTRIGHTCOM field looks like this:

ess easement to provide access to all lands in the vicinity in which
TNC has a fee and/or conservation easement interest.

(The first 282 characters have been truncated (including spaces).

This does not happen when I run the query in Sybase itself. And it
doesn't happen if I am only using the linked Tta table (but that
doesn't fit my query requirements).

Any ideas WHY this happens? Thanks in advance. Carol.

 

[Jeanette Cunningham]

Carol
This is standard behavior for memo fields in Access.
Whenever you ask Access to do some sort of comparison on memo fields you get
truncation.
I don't fully understand it. It is part of the way access works when it
works with memo fields.
Is there a way you can do the distinct part of the query without including
the memo field and then use the result of the distinct query to make a new
query including the memo field?

You can read about other problems with memo fields here

http://allenbrowne.com/ser-63.html


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[CP]

Thanks, Jeannette. I knew about the truncation from Access to Excel
but I guess I didn't realize that Access did the truncation too.

And I had only seen truncation done at the END of the field, never at
the front. I'll give your idea about two different queries a try.

Thanks. Carol.