Re: Truncating of memo fields

From: Marshall Barton (marshbarton_at_wowway.com)
Date: 05/20/04 

Date: Wed, 19 May 2004 22:20:24 -0500

Robin wrote:

>Marsh
I don't think it's the number of records that causes a
crash. There are just too many instances of reports with
many thousands of records with memo fields that are run
routinely without problems for that to trigger a bug by
itself.

I've never run into it myself, but I've heard that having a
lot of images in a report might do it, but you never
mentioned using image/picture controls.

I guess this might be a case of some kind of corruption,
either in the data tables or in some form/report/module, but
there's no good way to determine it. You might want to take
a look at Tony's FAQ on corruption causes and cures to see
if anything there makes the problem go away.

http://www.granite.ab.ca/access/corruptmdbs.htm 

-- 
Marsh
MVP [MS Access]
>The data is not being compared in the memo field although 
>that field can be queried on a string expression input by 
>the user in a form that will then display the results of 
>the query in a list box.
>
>I found a work around that consists of the following.
>
>A listbox on the form displays the results of the 
>original query. (This is a 'query by form' exercise).
>I use selected primay/foreign key data from this listbox 
>to run a seperate query that creates the table which is 
>the report's recordsource.
>
>This temporary table is created when the user clicks the 
>button on the form "Reports" (a little more complex than 
>that but I hope you get the gist).
>
>I have found that no truncation occurs when this happens.
>
>However, I have found another limitation in that if I try 
>to run this report with many records (> 150 it appears), 
>Access crashes and burns as it tries to format the 
>report.  It could be memory limitations on my PC (512Mb) 
>or it could be that it just overloads Access.  Anyway, 
>the upshot of it is that I get the 'Send Report to 
>Microsoft' error and the database crashes and burns.
>
>Any clues on how to overcome this problem other than 
>trapping the error if the listcount of the list box is > 
>150?
>
>Once again, thanks for your input.
>
>Robin 
>
>>-----Original Message-----
>>Robin wrote:
>>
>>>I have a database in which the text of a memo field is 
>>>the core of the database.  What has been placed in that 
>>>field is of prime concern to the users, the other data 
>>>around it while not peripheral is secondary to the data 
>>>in the memo field.  Therefore, they can search in whole 
>>>or in part on that field.  
>>>
>>>The problem is that when they want to send the results 
>of 
>>>the search to 
>>>1) a report
>>>2) to Word
>>>3) as a query
>>>the memo field is truncated which makes this whole 
>>>exercise redundant.  
>>
>>Not sure what you mean by "send the results", but the 
>first
>>thing you need to do is get a query to return the entire
>>memo field.  If the query does anything that requires the
>>memo field to be compared, it will be truncated to 255
>>characters.  The query operations that perform a compare
>>operation are DISTINCT, GROUP BY, ORDER BY, UNION, etc.  
>If
>>your query uses one of those keywords, try to find a way 
>to
>>avoid it, use an alternative the memo field (use First
>>instead of Group By, Left in the Order By, UNION 
>ALL, ???)
>>If that doesn't get you goping, post the query so we can 
>see
>>its exact syntax.
>>
>>Setting a memo field's Format property will also truncate
>>the returned results.