Subreport Notes Truncated - Microsoft Access Expert 9


	Back to Access Expert 9 Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Subreport Notes Truncated

Patricia Tonge

2 years ago

I am working on a simple database for others to use. I have a problem with one report which has a subreport. Both parent and child reports have notes fields. I have selected 'can grow' & 'can shrink' for both notes fields & both detail sections. Everything is working wonderfully apart from the fact that the notes in the subreport are truncated at 225 characters. I have Googled & it seems this is a bug & has been for years. I have seen a suggestion that changing font to Arial can help, but for me it did'nt. Have you any advice to overcome this problem? BTW both notes fields are Rich Text but even if I remove that setting it did not help. ALSO TO ADD A HUGE THANK YOU for all of the help on the website and I fully recommend the lessons (I am up to Expert Level but no expert as I am still learning). I could not have got my database to where it is now without you guys!

	Kevin Robertson @Reply 2 years ago
Have you also set Can Grow / Can Shrink to Yes for the Subreport object?

	Patricia Tonge OP @Reply 2 years ago
Hi Kevin. Thank you for the advice. I hadn't thought about that but have now checked. Can grow was already set to yes and can shrink is also now set to yes. It has not solved the issue though.

Kevin Robertson @Reply

2 years ago

Try to requery the subreport in the subreport's On Open event to refresh the data.

Private Sub Report_Open(Cancel As Integer)

    Me.Requery

End Sub

Or, in the parent report's Detail_Format event:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    SubreportName.Requery

End Sub

You could also try manually increasing the height of the Notes field to see if that helps.

Try running down Richard's Troubleshooter.

Patricia Tonge OP @Reply

2 years ago

Thanks again Kevin. I tried to requery the subreport copying and pasting your code but got "Run-Time error 2467. The expression you entered refers to an object that is closed or doesn't exist." When the report opened it was just the main form and no subform at all. I have deleted that code. I then tried the alternative you suggested but got error code "run-time error 424 Object required" and this time not even the main form. Maybe because I use a combo to select the specific subject of the report?? There was another message referring to a macro preventing rendering of the form. I have deleted that code too. I had previously tried making the Notes field taller but that made no odds.

Patricia Tonge OP @Reply

2 years ago

UPDATE - I opened the subreport as a report and have the same issue (I don't think I tested for this before adding it to the parent report). I have since created a new report based upon some of the data in the previous subreport, including the notes field. I have ensured both Can Grow/ Can Shrink are set to Yes for both the Notes field (which is Long Text) and for the Detail section. I still have the same issue. The Notes truncate at exactly the same point so I have obviously done something stupid somewhere and the problem is not related to including the subform in the parent form but something else. If nothing obvious comes to mind please dont waste time on this. I will work back through everything starting at table level to see if I can see what I have done. If I find the solution I will post it here in case it helps anyone else.

Patricia Tonge OP @Reply

2 years ago

FURTHER UPDATE - Having been unable to see anything amiss with the relevant table or the query upon which the problem report is based I tried to use the Datebase Documenter to print out the query for me to look at on paper. However I cannot get the report. I get an error message which reads "Too few parameters. Expected 1." I clicked OK and then had message "Unable to document Query "myquery". Continue?" I selected No as I was not sure what would happen if I selected Yes and there seemed little point if the Documenter was not going to provide the data. Given the problem with documenter I wonder if my query is the source of my problems. Am I best advised to delete the offending query and start afresh?

Kevin Yip @Reply

2 years ago

If you use SELECT DISTINCT, aggregate function, or any sort of change to the note fields in the query, they WILL be truncated to 255 characters. This is NOT a bug, but a feature in Access to limit the length of the text in order to perform said changes. A long text can hold one gigabyte of text, and Access can't keep that many characters when it needs to perform calculations and changes in a query. So, you need to check the query in your reports' record sources.

	Kevin Yip @Reply 2 years ago
... and remove any calculations or changes made to the long text fields.

Patricia Tonge OP @Reply

2 years ago

Thanks Kevin - There is no function in the query for the notes field nor any change to the notes field, unless "Group By" causes truncation? One of the other fields is set to show "Sum" another field is set to show "Max" yet another field has criteria "[forms]![TrustList2F]![NameOfOrganisation]" and there is one field where the result is based upon an expression. Should I take the notes field out of the query and use a different means to get it into the report? If so DLookUP to do this or I use am I asking too much of DLookUp?

Kevin Yip @Reply

2 years ago

GROUP BY is an aggregate operation, so it WILL truncate. You need to remove the long text fields from all your aggregate queries, then add them in with another query. For instance:

Make your aggregate query: SELECT ... FROM Table1 GROUP BY ...

Save it as Query1.

Then make another query, a plain SELECT query, that joins with Query1 and adds the long text fields:

SELECT longtext1, ... FROM Query1 INNER JOIN Table2 ...

	Patricia Tonge OP @Reply 2 years ago
Thanks Kevin!

	Patricia Tonge OP @Reply 2 years ago
Just wanted to confirm Kevin that your advice worked a treat! Thank you so much!!

	Kevin Yip @Reply 2 years ago
You're welcome. Below is more info on how queries truncate long text field and the workarounds: http://allenbrowne.com/ser-63.html

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Expert 9.

Next Unseen

New Feature: Comment Live View