Truncating of memo fields

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.

Does anyone know how to overcome this?

Thank you

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.
--
Marsh
MVP [MS Access]

Marsh
Thanks for your reply.

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.
--
Marsh
MVP [MS Access]
.

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.

Thanks Marsh,

I think it could be the dodgy hardware routine
even 'though I'm meant to have a state of the art machine
as I've had problems with Access over the last year with
this machine even though colleagues of mine using the
same machines don't get them while developing in Access.

Thanks for the URL. It sure is a comprehensive site.

Robin
-----Original Message-----
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.

.