Truncated memo fields from queries within queries

[Guest]

I needed to create a query to serve as the data source for a report. The
data needs to be compiled from a LOT of tables, so I figured I would simplify
my life by getting the data in pieces--I created one query to join four
tables, another query to join three other tables, etc. Then I use the
low-level queries as data sources for higher-level queries, until finally I
bring all the data together in a single top-level query, which provides the
data for the report.

This all worked out great except for one thing: My memo fields are getting
truncated to 255 characters. This doesn't happen when I use a single query
to get the data for a report; only when I "stack" the queries.

Does anybody know how to solve this problem without trying to put all the
queries together into one massive query-from-hell?

 

[Allen Browne]

If you ask it to perform any aggregation on a memo field, Access truncates
it to the first 255 characters.

If you have a Totals query, try choosing First instead of Group By under the
memo field. Instead of aggregating the values based on the memo field,
Access is then free to grab the first matching value and return the entire
thing. You will then need to change the field name in your report from (say)
Memo1 to FirstOfMemo1.

If there is no GROUP BY but the query contains DISTINCT, you will also have
the problem.

The other common cause is anything in the Format property of the text box on
the report, or in the Format property of the field in the table.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Guest]

Allen, thank you for your response.

The funny thing is that I experimented with a simple query without
aggregation of any sort and got the same result. If I specify something like
this:

SELECT (textfieldname), (textfieldname), (memofieldname)
FROM (tablename);

the resulting datasheet truncates the memo field data to 255 characters.
If, however, I use this query as the data source for a report, the memo
fields are NOT truncated on the report! If you happen to have a few minutes
to try this yourself, I would be interested in your results.

In the final analysis, I may need to construct the highest-level query with
joins to get the memo fields, but I was hoping to avoid this solution if
possible. Aside from the hassle, it seems inelegant.

If you happen to have any other insights or revelations, I would very much
appreciate your help. Thanks again.

 

[Allen Browne]

Hi Richard

I am not able to replicate the problem you describe with the query you gave.
I even tried adding the brackets just for fun, but the memo field was
returned in full.

Any chance the query where you saw this had a DISTINCT predicate?

If not, and it is reproducable, what version of Access?
And what version of msjet40.dll? (It's typically in windows\system32, and
its Properties has a Version tab.)

I would rely on JET to return the full memo field unless you ask for some
kind of aggregation on it.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Guest]

You're up a bit late, aren't you, Allen? I appreciate your help on this.

Interesting that you can't replicate the problem. I tried it again this
morning just to make sure I wasn't hallucinating, and it did the same thing.
There is no DISTINCT in the SELECT statement--it's exactly as I wrote it for
you in my previous note.

The Access version is 2002 (10.6501.6735) SP3.

The Jet version is 4.0.6508.0.

Is it possible for my company's implementation of Access or Jet to specify
memo field truncation in queries?

By the way, because of the fact that I need to get this report out to the
users ASAP, I went ahead this morning and created a higher-level query for my
report, adding the memo fields through joins, and the report now gets the
entire memo fields. However I'm still interested in a better solution to the
problem--the resulting query structure is positively unholy, and will reduce
speed and efficiency.

Thanks for burning the midnight oil for me, Allen. Cheers, mate.

 

[Allen Browne]

Okay, I was looking for an explanation for the differences in what we are
seeing.

I opened Northwind in A2002 SP3 (exactly the same version number), and ran:
SELECT Employees.EmployeeID,
Employees.LastName,
Employees.Notes,
Len([Notes]) AS MemoLen
FROM Employees;

It clearly returned memo Notes beyond 255 characters.

The only difference I can see is that I am using msjet40.dll version
4.0.8618.0.
You might try downloading SP8 for JET 4 from:
http://support.microsoft.com/gp/sp
http://support.microsoft.com/kb/239114

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

 

[Guest]

Well this is getting curioser and curioser.

I got into the Northwinds database and pasted your SELECT statement into a
query, and got length values beyond 255, just as you said. So I investigated
further, and discovered a new mystery.

When I bring up the datasheet for the Northwinds query and put the cursor in
one of the memo fields with a length of 400 or more, then hit the End key on
my keyboard, the cursor moves to a point in the memo that is NOT the end of
the memo field. Repeatedly hitting the End key moves it no farther. But if
I hit the right arrow key on my keyboard, the cursor moves farther into the
memo field.

The same thing happens in my database. Furthermore, the points in the memo
fields where the cursor stops after hitting the End key are the same points
where the memo fields are truncated when I stack the queries, and the same
points where the memo fields are truncated on the resulting report.

I might look into this further if I get more time, but if the light turns on
for you, I would appreciate your insights.
--
Richard Reinertson
Data Modeler - Fidelity Investments
Smithfield, Rhode Island, USA

Okay, I was looking for an explanation for the differences in what we are
seeing.

I opened Northwind in A2002 SP3 (exactly the same version number), and ran:
SELECT Employees.EmployeeID,
Employees.LastName,
Employees.Notes,
Len([Notes]) AS MemoLen
FROM Employees;

It clearly returned memo Notes beyond 255 characters.

The only difference I can see is that I am using msjet40.dll version
4.0.8618.0.
You might try downloading SP8 for JET 4 from:
http://support.microsoft.com/gp/sp
http://support.microsoft.com/kb/239114

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

You're up a bit late, aren't you, Allen? I appreciate your help on this.

Interesting that you can't replicate the problem. I tried it again this
morning just to make sure I wasn't hallucinating, and it did the same
thing.
There is no DISTINCT in the SELECT statement--it's exactly as I wrote it
for
you in my previous note.

The Access version is 2002 (10.6501.6735) SP3.

The Jet version is 4.0.6508.0.

Is it possible for my company's implementation of Access or Jet to specify
memo field truncation in queries?

By the way, because of the fact that I need to get this report out to the
users ASAP, I went ahead this morning and created a higher-level query for
my
report, adding the memo fields through joins, and the report now gets the
entire memo fields. However I'm still interested in a better solution to
the
problem--the resulting query structure is positively unholy, and will
reduce
speed and efficiency.

Thanks for burning the midnight oil for me, Allen. Cheers, mate.

 

[Allen Browne]

What happens if you drag the height of the row downwards in the query's
datasheet view? Is this just where the word breaks are?

Do you have any form with a Timer event running?

Shift+F2 opens the zoom box in any field.

If it were a problem with the video driver (so metrics), it might not occur
on a different machine or perhaps even at a different screen resolution.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

Well this is getting curioser and curioser.

I got into the Northwinds database and pasted your SELECT statement into a
query, and got length values beyond 255, just as you said. So I
investigated
further, and discovered a new mystery.

When I bring up the datasheet for the Northwinds query and put the cursor
in
one of the memo fields with a length of 400 or more, then hit the End key
on
my keyboard, the cursor moves to a point in the memo that is NOT the end
of
the memo field. Repeatedly hitting the End key moves it no farther. But
if
I hit the right arrow key on my keyboard, the cursor moves farther into
the
memo field.

The same thing happens in my database. Furthermore, the points in the
memo
fields where the cursor stops after hitting the End key are the same
points
where the memo fields are truncated when I stack the queries, and the same
points where the memo fields are truncated on the resulting report.

I might look into this further if I get more time, but if the light turns
on
for you, I would appreciate your insights.
--
Richard Reinertson
Data Modeler - Fidelity Investments
Smithfield, Rhode Island, USA

Okay, I was looking for an explanation for the differences in what we are
seeing.

I opened Northwind in A2002 SP3 (exactly the same version number), and
ran:
SELECT Employees.EmployeeID,
Employees.LastName,
Employees.Notes,
Len([Notes]) AS MemoLen
FROM Employees;

It clearly returned memo Notes beyond 255 characters.

The only difference I can see is that I am using msjet40.dll version
4.0.8618.0.
You might try downloading SP8 for JET 4 from:
http://support.microsoft.com/gp/sp
http://support.microsoft.com/kb/239114

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

in
message

You're up a bit late, aren't you, Allen? I appreciate your help on
this.

Interesting that you can't replicate the problem. I tried it again
this
morning just to make sure I wasn't hallucinating, and it did the same
thing.
There is no DISTINCT in the SELECT statement--it's exactly as I wrote
it
for
you in my previous note.

The Access version is 2002 (10.6501.6735) SP3.

The Jet version is 4.0.6508.0.

Is it possible for my company's implementation of Access or Jet to
specify
memo field truncation in queries?

By the way, because of the fact that I need to get this report out to
the
users ASAP, I went ahead this morning and created a higher-level query
for
my
report, adding the memo fields through joins, and the report now gets
the
entire memo fields. However I'm still interested in a better solution
to
the
problem--the resulting query structure is positively unholy, and will
reduce
speed and efficiency.

Thanks for burning the midnight oil for me, Allen. Cheers, mate.



:

Hi Richard

I am not able to replicate the problem you describe with the query you
gave.
I even tried adding the brackets just for fun, but the memo field was
returned in full.

Any chance the query where you saw this had a DISTINCT predicate?

If not, and it is reproducable, what version of Access?
And what version of msjet40.dll? (It's typically in windows\system32,
and
its Properties has a Version tab.)

I would rely on JET to return the full memo field unless you ask for
some
kind of aggregation on it.

"Richard Reinertson" <[email protected]>
wrote
in
message
Allen, thank you for your response.

The funny thing is that I experimented with a simple query without
aggregation of any sort and got the same result. If I specify
something
like
this:

SELECT (textfieldname), (textfieldname), (memofieldname)
FROM (tablename);

the resulting datasheet truncates the memo field data to 255
characters.
If, however, I use this query as the data source for a report, the
memo
fields are NOT truncated on the report! If you happen to have a few
minutes
to try this yourself, I would be interested in your results.

In the final analysis, I may need to construct the highest-level
query
with
joins to get the memo fields, but I was hoping to avoid this
solution
if
possible. Aside from the hassle, it seems inelegant.

If you happen to have any other insights or revelations, I would
very
much
appreciate your help. Thanks again.




:

If you ask it to perform any aggregation on a memo field, Access
truncates
it to the first 255 characters.

If you have a Totals query, try choosing First instead of Group By
under
the
memo field. Instead of aggregating the values based on the memo
field,
Access is then free to grab the first matching value and return the
entire
thing. You will then need to change the field name in your report
from
(say)
Memo1 to FirstOfMemo1.

If there is no GROUP BY but the query contains DISTINCT, you will
also
have
the problem.

The other common cause is anything in the Format property of the
text
box
on
the report, or in the Format property of the field in the table.

"Richard Reinertson" <[email protected]>
wrote
in
message
I needed to create a query to serve as the data source for a
report.
The
data needs to be compiled from a LOT of tables, so I figured I
would
simplify
my life by getting the data in pieces--I created one query to
join
four
tables, another query to join three other tables, etc. Then I
use
the
low-level queries as data sources for higher-level queries, until
finally
I
bring all the data together in a single top-level query, which
provides
the
data for the report.

This all worked out great except for one thing: My memo fields
are
getting
truncated to 255 characters. This doesn't happen when I use a
single
query
to get the data for a report; only when I "stack" the queries.

Does anybody know how to solve this problem without trying to put
all
the
queries together into one massive query-from-hell?

 

[Guest]

No, the word breaks are outside the visible view for the column. If I drag
the border to increase the row height, it takes me beyond the end of the
truncation and leaves white space at the bottom of the row.

I'm not using any Timer events in this app.

I haven't discovered much else about this, except that it appears to result
from using a UNION in my SQL. Before the union, memo fields are complete;
after the union the longer ones are truncated. I haven't checked whether
this is a documented limitation, or if there's any built-in feature to
circumvent it.

What happens if you drag the height of the row downwards in the query's
datasheet view? Is this just where the word breaks are?

Do you have any form with a Timer event running?

Shift+F2 opens the zoom box in any field.

If it were a problem with the video driver (so metrics), it might not occur
on a different machine or perhaps even at a different screen resolution.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message

Well this is getting curioser and curioser.

I got into the Northwinds database and pasted your SELECT statement into a
query, and got length values beyond 255, just as you said. So I
investigated
further, and discovered a new mystery.

When I bring up the datasheet for the Northwinds query and put the cursor
in
one of the memo fields with a length of 400 or more, then hit the End key
on
my keyboard, the cursor moves to a point in the memo that is NOT the end
of
the memo field. Repeatedly hitting the End key moves it no farther. But
if
I hit the right arrow key on my keyboard, the cursor moves farther into
the
memo field.

The same thing happens in my database. Furthermore, the points in the
memo
fields where the cursor stops after hitting the End key are the same
points
where the memo fields are truncated when I stack the queries, and the same
points where the memo fields are truncated on the resulting report.

I might look into this further if I get more time, but if the light turns
on
for you, I would appreciate your insights.
--
Richard Reinertson
Data Modeler - Fidelity Investments
Smithfield, Rhode Island, USA

Okay, I was looking for an explanation for the differences in what we are
seeing.

I opened Northwind in A2002 SP3 (exactly the same version number), and
ran:
SELECT Employees.EmployeeID,
Employees.LastName,
Employees.Notes,
Len([Notes]) AS MemoLen
FROM Employees;

It clearly returned memo Notes beyond 255 characters.

The only difference I can see is that I am using msjet40.dll version
4.0.8618.0.
You might try downloading SP8 for JET 4 from:
http://support.microsoft.com/gp/sp
http://support.microsoft.com/kb/239114

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

in
message
You're up a bit late, aren't you, Allen? I appreciate your help on
this.

Interesting that you can't replicate the problem. I tried it again
this
morning just to make sure I wasn't hallucinating, and it did the same
thing.
There is no DISTINCT in the SELECT statement--it's exactly as I wrote
it
for
you in my previous note.

The Access version is 2002 (10.6501.6735) SP3.

The Jet version is 4.0.6508.0.

Is it possible for my company's implementation of Access or Jet to
specify
memo field truncation in queries?

By the way, because of the fact that I need to get this report out to
the
users ASAP, I went ahead this morning and created a higher-level query
for
my
report, adding the memo fields through joins, and the report now gets
the
entire memo fields. However I'm still interested in a better solution
to
the
problem--the resulting query structure is positively unholy, and will
reduce
speed and efficiency.

Thanks for burning the midnight oil for me, Allen. Cheers, mate.



:

Hi Richard

I am not able to replicate the problem you describe with the query you
gave.
I even tried adding the brackets just for fun, but the memo field was
returned in full.

Any chance the query where you saw this had a DISTINCT predicate?

If not, and it is reproducable, what version of Access?
And what version of msjet40.dll? (It's typically in windows\system32,
and
its Properties has a Version tab.)

I would rely on JET to return the full memo field unless you ask for
some
kind of aggregation on it.

"Richard Reinertson" <[email protected]>
wrote
in
message
Allen, thank you for your response.

The funny thing is that I experimented with a simple query without
aggregation of any sort and got the same result. If I specify
something
like
this:

SELECT (textfieldname), (textfieldname), (memofieldname)
FROM (tablename);

the resulting datasheet truncates the memo field data to 255
characters.
If, however, I use this query as the data source for a report, the
memo
fields are NOT truncated on the report! If you happen to have a few
minutes
to try this yourself, I would be interested in your results.

In the final analysis, I may need to construct the highest-level
query
with
joins to get the memo fields, but I was hoping to avoid this
solution
if
possible. Aside from the hassle, it seems inelegant.

If you happen to have any other insights or revelations, I would
very
much
appreciate your help. Thanks again.




:

If you ask it to perform any aggregation on a memo field, Access
truncates
it to the first 255 characters.

If you have a Totals query, try choosing First instead of Group By
under
the
memo field. Instead of aggregating the values based on the memo
field,
Access is then free to grab the first matching value and return the
entire
thing. You will then need to change the field name in your report
from
(say)
Memo1 to FirstOfMemo1.

If there is no GROUP BY but the query contains DISTINCT, you will
also
have
the problem.

The other common cause is anything in the Format property of the
text
box
on
the report, or in the Format property of the field in the table.

"Richard Reinertson" <[email protected]>
wrote
in
message
I needed to create a query to serve as the data source for a
report.
The
data needs to be compiled from a LOT of tables, so I figured I
would
simplify
my life by getting the data in pieces--I created one query to
join
four
tables, another query to join three other tables, etc. Then I
use
the
low-level queries as data sources for higher-level queries, until
finally
I
bring all the data together in a single top-level query, which
provides
the
data for the report.

This all worked out great except for one thing: My memo fields
are
getting
truncated to 255 characters. This doesn't happen when I use a
single
query
to get the data for a report; only when I "stack" the queries.

Does anybody know how to solve this problem without trying to put
all
the
queries together into one massive query-from-hell?

 

[Allen Browne]

Ah: this is a UNION query.

Does it make any difference if you use UNION ALL instead of UNION?

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

message
news:[email protected]...

 

[Guest]

Have just read through this from a google groups search. Had exactly same
problem with Union Query and truncated memo fields.
As you suggested Allen, it was solved by Union ALL.
Perhaps you might explain why this is the case?
I think I'm right is thinking that All would not normally be required for
simple Unions of matching fields.
Thanks for the help
Madhouse

 

[John Spencer]

Union removes duplicate rows. To do so, it truncates memo fields to 255
characters so it doesn't have to deal with fields that potentially have
megabytes of data.

UNION ALL does not remove duplicate rows.

This same behavior is seen with SELECT Distinct and with aggregate query
functions such as Max and Min and with Group By fields.