Problem with Reports

[Walt]

Need some help with reports.

My report is not showing all of the text from my table. The field in my
table is a memo data type, so it has a lot of text in it. I checked the
table and all of the information is in it but only 1/3 of it shows on the
report. Does anyone know why that happens?

Thanks.

 

[Philip Herlihy]

Need some help with reports.

My report is not showing all of the text from my table. The field in my
table is a memo data type, so it has a lot of text in it. I checked the
table and all of the information is in it but only 1/3 of it shows on the
report. Does anyone know why that happens?

Thanks.

First thing to check is whether the text box on the report is big enough
to hold the text. You don't have the option to scroll on a report, but
you can set the "can grow" property to Yes.

Phil, London

 

[John W. Vinson]

Need some help with reports.

My report is not showing all of the text from my table. The field in my
table is a memo data type, so it has a lot of text in it. I checked the
table and all of the information is in it but only 1/3 of it shows on the
report. Does anyone know why that happens?

Thanks.

A Memo field will be truncated to 255 characters if you apply ANY Format to
it. Be sure that the Format property is blank in both the textbox on the
report and in the Table and Query containing the field.

You will also get truncation if you try to sort or group by the memo field. If
the report is based on a Totals query use First rather than Group By on the
totals line.

 

[Walt]

The format properties are empty, the box is set to grow, and I made the box

 

[ken]

If you have used the report wizard, or have added the memo field to
the report in design view by dragging from the field list you may have
encountered a long-standing but intermittent bug which causes this
behaviour. Delete the control bound to the memo field. add a text box
control from the toolbox and set its ControlSource property to the
name of the memo field.

Ken Sheridan
Stafford, England

 

[Walt]

Hi Ken,

I did that and still got the same problem. I re-looked at my query and
found that it is truncating the text in the query as well so I now think the
problem is there versus in the report. Here's what my query looks like (it's
one you helped me build):

SELECT DISTINCT [tbl.Sources].[Source],
[tbl_Recommendations].[Recommendation],
[tbl_Accomplishments].[Accomplishment] FROM tbl_Sources INNER JOIN
(tbl_Recommendations INNER JOIN (tbl_Accomplishments INNER JOIN tbl_master ON
[tbl_Accomplishments].[AccomplishmentsID]=[tbl_master].[AccomplishmentID])
ON [tbl_Recommendations].[RecommendationID]=[tbl.master].[RecommendationID])
ON [tbl.Sources].[SourceID]=[tbl_master].[SourceID]
WHERE ((([tbl_Sources].[Source])=[Forms]![frm_ICA_Search]![cboSource] Or
[Forms]![frm_ICA_Search]![cboSource] Is Null) And
(([tbl_Recommendations].[Recommendation]) Like "*"
&[Forms]![frm_ICA_Search]![txtRec]& "*" Or [Forms]![frm_ICA_Search]![txtRec]
Is Null) And (([tbl_Accomplishments].[Accomplishment]) Like "*" &
[Forms]![frm_ICA_Search]![txtAcc]& "*" Or [Forms]![frm_ICA_Search]![txtAcc]
Is Null)) ORDER BY [tbl_Sources].[Source];

Thanks for any assistance.

Tony

 

[John Spencer MVP]

The DISTINCT keyword in the query will truncate the display of memo fields to
255 characters.

Try removing DISTINCT from the query and see if that solves your problem.

If not, you can use an aggregate (totals) query that may work for you. I'll
assume that Accomplishment is the memo field. If you want any Accomplishment
from the group as determined by Source plus Recommendation then the following
will probably work for you.

SELECT [tbl.Sources].[Source],
[tbl_Recommendations].[Recommendation],
First([tbl_Accomplishments].[Accomplishment]) as fAccomplishment
FROM tbl_Sources INNER JOIN
(tbl_Recommendations INNER JOIN (tbl_Accomplishments
INNER JOIN tbl_master
ON [tbl_Accomplishments].[AccomplishmentsID]=[tbl_master].[AccomplishmentID])
ON [tbl_Recommendations].[RecommendationID]=[tbl.master].[RecommendationID])
ON [tbl.Sources].[SourceID]=[tbl_master].[SourceID]
WHERE ((([tbl_Sources].[Source])=[Forms]![frm_ICA_Search]![cboSource] Or
[Forms]![frm_ICA_Search]![cboSource] Is Null)

And (([tbl_Recommendations].[Recommendation]) Like "*" &
[Forms]![frm_ICA_Search]![txtRec]& "*"
Or [Forms]![frm_ICA_Search]![txtRec] Is Null)

And (([tbl_Accomplishments].[Accomplishment]) Like "*" &
[Forms]![frm_ICA_Search]![txtAcc]& "*"
Or [Forms]![frm_ICA_Search]![txtAcc] Is Null))

GROUP BY [tbl.Sources].[Source],
[tbl_Recommendations].[Recommendation]
ORDER BY [tbl_Sources].[Source]

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

SELECT DISTINCT

THAT'S the problem. If you select DISTINCT memo fields, it will truncate them
to 255 bytes so the memo fields in different records can be compared without
having to allow for the possiblity that they're gigabytes in size.

If you can get the results you want, just remove the DISTINCT. If you want to
collapse multiple records into one for the report, and it doesn't matter which
record contains the memo field, use a TOTALS query instead and use the "First"
option on the Totals row for the memo field. If the only distinction between
records is in the 221890th character of the memo fields... you're out of luck.

 

[Walt]

John & John

Thank you both. Removing Distinct resolved the issue.