Text truncated in query

E

Eric G

I have two separate forms based on two distinct select queries. Both draw
from the same table. Query 1 draws straight from the table with no joins.
Query 2 has a join to another table. Memo field text in the query with the
join is truncated at 255 characters whereas text in the query with no join is
fine. There is no sorting, calculating or anything in either query.

Can anyone advise me on this?
 
M

Marshall Barton

Eric G said:
I have two separate forms based on two distinct select queries. Both draw
from the same table. Query 1 draws straight from the table with no joins.
Query 2 has a join to another table. Memo field text in the query with the
join is truncated at 255 characters whereas text in the query with no join is
fine. There is no sorting, calculating or anything in either query.
Click to expand...


That will also happen if you use the query keyword DISTINCT,
GROUP BY or a few other things.

Another cause is using a Format setting for the memo's text
box in the report.
 
E

Eric G

This is extremely interesting! I viewed the query's SQL, took out the
'DISTINCT' keyword, ran the query and it eliminated the problem. I am
wondering 1) if this behavior is by design and if so what purpose it serves
and, 2) what does does the 'DISTINCT' keyword do?

Thanks.

Eric
 
M

Marshall Barton

The purpose of the DISTINCT keyword is to eliminate
duplicate records in the query's result set. It should only
be used when you have a specific need for its functionality.

This requires that Access compare every field in every
record to see if they are the same. It's the comparison of
memo fields that causes the truncation. That's why using a
memo in the ORDER BY or GROUP BY clauses also causes
truncation.
 
C

Craig Beyers

I'm not using DISTINCT but am using "Group by"; none of the other options in
the query applies to memo fields (I've already tried First and Last and the
report using the query requests a value...which should be coming from the
query)

So how do I get the full memo field data as a result of running the query so
I can run the report?

Thanks.

CPB

Marshall Barton said:
The purpose of the DISTINCT keyword is to eliminate
duplicate records in the query's result set. It should only
be used when you have a specific need for its functionality.

This requires that Access compare every field in every
record to see if they are the same. It's the comparison of
memo fields that causes the truncation. That's why using a
memo in the ORDER BY or GROUP BY clauses also causes
truncation.
--
Marsh
MVP [MS Access]


Eric said:
This is extremely interesting! I viewed the query's SQL, took out the
'DISTINCT' keyword, ran the query and it eliminated the problem. I am
wondering 1) if this behavior is by design and if so what purpose it serves
and, 2) what does does the 'DISTINCT' keyword do?
Click to expand...
Click to expand...
 
D

Duane Hookom

If you must use Group By, then choose First for the Totals of all memo
fields. Fix the "report using the query requests a value" so you don't get
the error. You are changing a column name so fix it in the report or query.

--
Duane Hookom
MS Access MVP
--

Craig Beyers said:
I'm not using DISTINCT but am using "Group by"; none of the other options
in
the query applies to memo fields (I've already tried First and Last and
the
report using the query requests a value...which should be coming from the
query)

So how do I get the full memo field data as a result of running the query
so
I can run the report?

Thanks.

CPB

Marshall Barton said:
The purpose of the DISTINCT keyword is to eliminate
duplicate records in the query's result set. It should only
be used when you have a specific need for its functionality.

This requires that Access compare every field in every
record to see if they are the same. It's the comparison of
memo fields that causes the truncation. That's why using a
memo in the ORDER BY or GROUP BY clauses also causes
truncation.
--
Marsh
MVP [MS Access]


Eric said:
This is extremely interesting! I viewed the query's SQL, took out the
'DISTINCT' keyword, ran the query and it eliminated the problem. I am
wondering 1) if this behavior is by design and if so what purpose it
serves
and, 2) what does does the 'DISTINCT' keyword do?



I have two separate forms based on two distinct select queries. Both
draw
from the same table. Query 1 draws straight from the table with no
joins.
Query 2 has a join to another table. Memo field text in the query
with the
join is truncated at 255 characters whereas text in the query with no
join is
fine. There is no sorting, calculating or anything in either query.


:
That will also happen if you use the query keyword DISTINCT,
GROUP BY or a few other things.

Another cause is using a Format setting for the memo's text
box in the report.
Click to expand...
Click to expand...
Click to expand...
 
C

Craig Beyers

Thanks. I went back to the queries and reports and used "First" for the memo
fields in the query and changed the report fields to use the "First
of...[data field]" and things seem to work...in all the similar queries and
reports.

CPB

Duane Hookom said:
If you must use Group By, then choose First for the Totals of all memo
fields. Fix the "report using the query requests a value" so you don't get
the error. You are changing a column name so fix it in the report or query.

--
Duane Hookom
MS Access MVP
--

Craig Beyers said:
I'm not using DISTINCT but am using "Group by"; none of the other options
in
the query applies to memo fields (I've already tried First and Last and
the
report using the query requests a value...which should be coming from the
query)

So how do I get the full memo field data as a result of running the query
so
I can run the report?

Thanks.

CPB

Marshall Barton said:
The purpose of the DISTINCT keyword is to eliminate
duplicate records in the query's result set. It should only
be used when you have a specific need for its functionality.

This requires that Access compare every field in every
record to see if they are the same. It's the comparison of
memo fields that causes the truncation. That's why using a
memo in the ORDER BY or GROUP BY clauses also causes
truncation.
--
Marsh
MVP [MS Access]


Eric G wrote:
This is extremely interesting! I viewed the query's SQL, took out the
'DISTINCT' keyword, ran the query and it eliminated the problem. I am
wondering 1) if this behavior is by design and if so what purpose it
serves
and, 2) what does does the 'DISTINCT' keyword do?



I have two separate forms based on two distinct select queries. Both
draw
from the same table. Query 1 draws straight from the table with no
joins.
Query 2 has a join to another table. Memo field text in the query
with the
join is truncated at 255 characters whereas text in the query with no
join is
fine. There is no sorting, calculating or anything in either query.


:
That will also happen if you use the query keyword DISTINCT,
GROUP BY or a few other things.

Another cause is using a Format setting for the memo's text
box in the report.
Click to expand...
Click to expand...
Click to expand...
 
Top