Memo field gets truncated in report

[M Skabialka]

In Access 2000 I created a new report which has to show the contents of a
memo field called [Notes].

The query shows all of the data, which can be 300 or 400 characters
sometimes, but the report cuts it off at 150 characters, the width of the
page.

So I tried in the query to break up [Notes] into
Notes1: mid([Notes,1,255)
Notes2: mid([Notes,256,510)

I also tried:
Notes1: mid([Notes,1,150)
Notes2: mid([Notes,151,300)

and in the report the control source is
NotesTxt
=[Notes1] & [Notes2]

but it still gets cut off at 150 characters. The field is set to grow or
shrink as needed, but even if I make the field very large the words still
get chopped off.

What am I missing here?
Thanks
Mich

 

[Allen Browne]

The memo field in the report will be truncated if any of the following are
true:
- The field in the table has anything in its Format property.

- The text box on your report has anything in its Format property.

- The query uses a function such as UCase() around the memo field.

- The query is a Totals query, and uses GROUP BY on the memo.

- The query performs other aggregation, such as a DISTINCT clause.

- The report performs other aggregation, such as a group header on the memo.

If the problem is with the GROUP BY clause, try using First instead of Group
By under your memo. The output field will change name (e.g. to
FirstOfMyMemo), so you will need to change the Control Source of the text
box on your report, but it will solve the problem.

 

[Catherine Cook]

have you tried setting the report SECTION the memo field
is in to allow growth (not just the field)?

 

[M Skabialka]

This is what I am doing:
The users keys in a search word, e.g. 'engineer'
A query (qrySearchCriteriaID) runs which looks in half a dozen fields for
this word (Notes, Occupation, Company, etc in a table and related many to
one table) and return the Record ID of the contact person only. This uses
DISTINCT so only one of each Record ID is returned, no matter which field
the search term is found in.

The next query is:
SELECT Contacts.* FROM Contacts INNER JOIN qrySearchCriteriaID ON
(Contacts.ContactID = qrySearchCriteriaID.ContactID) AND (Contacts.ContactID
= qrySearchCriteriaID.ContactID);

Fields in Contacts are Notes, Occupation, Company, etc.

So based on your suggestions below, I am using a DISTINCT in the first
query, but when I look at the information in the query, there is no missing
info; 290 character notes are complete, etc, but they get truncated on the
report.
If I say Can Grow = Yes, and Can Shrink = yes, I get no text. If Can Shrink
= No I get the first line of text only. Even if I make the field an inch
high and the page width, I never get the full 290 characters.

Can that first DISTINCT really make that much difference in the report when
I don't see it in the query?

Still puzzled here...
Michele

The memo field in the report will be truncated if any of the following are
true:
- The field in the table has anything in its Format property.

- The text box on your report has anything in its Format property.

- The query uses a function such as UCase() around the memo field.

- The query is a Totals query, and uses GROUP BY on the memo.

- The query performs other aggregation, such as a DISTINCT clause.

- The report performs other aggregation, such as a group header on the memo.

If the problem is with the GROUP BY clause, try using First instead of Group
By under your memo. The output field will change name (e.g. to
FirstOfMyMemo), so you will need to change the Control Source of the text
box on your report, but it will solve the problem.

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

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

In Access 2000 I created a new report which has to show the contents of a
memo field called [Notes].

The query shows all of the data, which can be 300 or 400 characters
sometimes, but the report cuts it off at 150 characters, the width of the
page.

So I tried in the query to break up [Notes] into
Notes1: mid([Notes,1,255)
Notes2: mid([Notes,256,510)

I also tried:
Notes1: mid([Notes,1,150)
Notes2: mid([Notes,151,300)

and in the report the control source is
NotesTxt
=[Notes1] & [Notes2]

but it still gets cut off at 150 characters. The field is set to grow or
shrink as needed, but even if I make the field very large the words still
get chopped off.

What am I missing here?
Thanks
Mich

 

[Allen Browne]

The queries look fine.

Presumably qrySearchCriteriaID is returning the ContactID only (not the memo
fields), so the DISTINCT is being performed on the ConactID only, i.e. it is
not aggregating on the memo fields. The next query adds in the memo fields,
but does no aggregating, so it is fine too.

The aggregation must be happening at the report level. What do you have in
the Sorting'nGrouping that could cause the report to group by the memo
fields?

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

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

This is what I am doing:
The users keys in a search word, e.g. 'engineer'
A query (qrySearchCriteriaID) runs which looks in half a dozen fields for
this word (Notes, Occupation, Company, etc in a table and related many to
one table) and return the Record ID of the contact person only. This uses
DISTINCT so only one of each Record ID is returned, no matter which field
the search term is found in.

The next query is:
SELECT Contacts.* FROM Contacts INNER JOIN qrySearchCriteriaID ON
(Contacts.ContactID = qrySearchCriteriaID.ContactID) AND (Contacts.ContactID
= qrySearchCriteriaID.ContactID);

Fields in Contacts are Notes, Occupation, Company, etc.

So based on your suggestions below, I am using a DISTINCT in the first
query, but when I look at the information in the query, there is no missing
info; 290 character notes are complete, etc, but they get truncated on the
report.
If I say Can Grow = Yes, and Can Shrink = yes, I get no text. If Can Shrink
= No I get the first line of text only. Even if I make the field an inch
high and the page width, I never get the full 290 characters.

Can that first DISTINCT really make that much difference in the report when
I don't see it in the query?

Still puzzled here...
Michele

The memo field in the report will be truncated if any of the following are
true:
- The field in the table has anything in its Format property.

- The text box on your report has anything in its Format property.

- The query uses a function such as UCase() around the memo field.

- The query is a Totals query, and uses GROUP BY on the memo.

- The query performs other aggregation, such as a DISTINCT clause.

- The report performs other aggregation, such as a group header on the memo.

If the problem is with the GROUP BY clause, try using First instead of Group
By under your memo. The output field will change name (e.g. to
FirstOfMyMemo), so you will need to change the Control Source of the text
box on your report, but it will solve the problem.

of

a

memo field called [Notes].

The query shows all of the data, which can be 300 or 400 characters
sometimes, but the report cuts it off at 150 characters, the width of the
page.

So I tried in the query to break up [Notes] into
Notes1: mid([Notes,1,255)
Notes2: mid([Notes,256,510)

I also tried:
Notes1: mid([Notes,1,150)
Notes2: mid([Notes,151,300)

and in the report the control source is
NotesTxt
=[Notes1] & [Notes2]

but it still gets cut off at 150 characters. The field is set to grow or
shrink as needed, but even if I make the field very large the words still
get chopped off.

What am I missing here?
Thanks
Mich

 

[M Skabialka]

I noticed the Contact Name, Notes and Occupation were grouped, even though I
had a sub-report, so moved them out of the group and into Details. Now the
full text of Notes was showing up.

Then I noticed my On Print formatting wasn't working - it applied to those
fields when they were in the Contact Name section. As soon as I moved the
On Print code into the detail section the problem came back - the notes get
truncated!

My code:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)

If InStr(1, Me!NotesTxt, Forms!frmReports!SearchCriteria) > 0 Then
NotesTxt.FontBold = True
Else
NotesTxt.FontBold = False
End If

End Sub

So I removed this code for now.
But, why should the Bold format cause the fields to truncate?

Mich

The queries look fine.

Presumably qrySearchCriteriaID is returning the ContactID only (not the memo
fields), so the DISTINCT is being performed on the ConactID only, i.e. it is
not aggregating on the memo fields. The next query adds in the memo fields,
but does no aggregating, so it is fine too.

The aggregation must be happening at the report level. What do you have in
the Sorting'nGrouping that could cause the report to group by the memo
fields?

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

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

This is what I am doing:
The users keys in a search word, e.g. 'engineer'
A query (qrySearchCriteriaID) runs which looks in half a dozen fields for
this word (Notes, Occupation, Company, etc in a table and related many to
one table) and return the Record ID of the contact person only. This uses
DISTINCT so only one of each Record ID is returned, no matter which field
the search term is found in.

The next query is:
SELECT Contacts.* FROM Contacts INNER JOIN qrySearchCriteriaID ON
(Contacts.ContactID = qrySearchCriteriaID.ContactID) AND (Contacts.ContactID
= qrySearchCriteriaID.ContactID);

Fields in Contacts are Notes, Occupation, Company, etc.

So based on your suggestions below, I am using a DISTINCT in the first
query, but when I look at the information in the query, there is no missing
info; 290 character notes are complete, etc, but they get truncated on the
report.
If I say Can Grow = Yes, and Can Shrink = yes, I get no text. If Can Shrink
= No I get the first line of text only. Even if I make the field an inch
high and the page width, I never get the full 290 characters.

Can that first DISTINCT really make that much difference in the report when
I don't see it in the query?

Still puzzled here...
Michele

The memo field in the report will be truncated if any of the following are
true:
- The field in the table has anything in its Format property.

- The text box on your report has anything in its Format property.

- The query uses a function such as UCase() around the memo field.

- The query is a Totals query, and uses GROUP BY on the memo.

- The query performs other aggregation, such as a DISTINCT clause.

- The report performs other aggregation, such as a group header on the memo.

If the problem is with the GROUP BY clause, try using First instead of Group
By under your memo. The output field will change name (e.g. to
FirstOfMyMemo), so you will need to change the Control Source of the text
box on your report, but it will solve the problem.

In Access 2000 I created a new report which has to show the contents

of

a
memo field called [Notes].

The query shows all of the data, which can be 300 or 400 characters
sometimes, but the report cuts it off at 150 characters, the width

of
the

page.

So I tried in the query to break up [Notes] into
Notes1: mid([Notes,1,255)
Notes2: mid([Notes,256,510)

I also tried:
Notes1: mid([Notes,1,150)
Notes2: mid([Notes,151,300)

and in the report the control source is
NotesTxt
=[Notes1] & [Notes2]

but it still gets cut off at 150 characters. The field is set to

grow
or

shrink as needed, but even if I make the field very large the words still
get chopped off.

What am I missing here?
Thanks
Mich