transfer spreadsheet

[Guest]

When running the docmd outputto or transferspreadsheet statements from within
a module or macro the memo fields get truncated whether from a table or
query into excel. The query is a straight query no grouping, sorting,
calculating etc. When I use the menu to export the same table or query
everything comes over fine. But when trying to do this from a macro or module
they are truncated. One obsevation: I can recreate the problem when running
it from the menu by checking the formatting box. Looking at the output from
using the statements it looks like that the output is indeed formatted. Is
there anyway to turn this off when running outputto or transferspreadsheet
from a macro or module? I'm using Office 2000 professional. Thanks!

 

[Allen Browne]

Sounds like this problem, which requires you to download the free service
packs:
ACC2000: When You Export an Access Report the Memo Field Is Truncated
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;281153

Although the article does not say so, be sure to get the JET 4 Service Pack
8 as well. It also fixes many probems.

 

[Guest]

Thanks for this tip. But not out of the woods yet. I have confirmed that I'm
at MS Access 2000 (9.0.7616 SP-3 ) and my Jet 4 SP shows version 4.0.8618.0
(Security Bulletin MS04-014). I did try to install Jet 4 SP8 anyway and got
message saying that a lower version of this service pack was detected.. and
that I needed to have at least SP3 to be able to apply this SP. Is it
possible to be at version 4.0.8618.0 without SP8 applied? Also, so far I am
able to find Jet 4.0 SP3 to install?

Thanks Again ! Ernie

 

[Allen Browne]

Okay, Ernie. You have SP3 for Office 2000, and also SP8 for JET 4.
That part is fine, so it must be another factor.

The knowledgebase also contains this article:
ACC2000: Memo Field Truncated When Report Is Output to Excel
at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;208801
explaining that the file format you export to might be the issue.

You might also like to open your table in design view, and check there is
nothing in the Format property of the field. Anything there can cause
truncation at 255 characters.

Similarly, if you are exporting a query, the query will truncate the memo
fields if it has to perform any aggregation on the memo field. That includes
a DISTINCT predicate, or naming the memo field in the GROUP BY clause. (You
can use First under the memo in a Totals query without truncating it.)

 

[Guest]

Allen, Thanks for the Help. The article describes the issue I'm having. I
wasn't aware that "Access outputs a report to Excel 5.0/95 format, in which
the maximum length of text-cell contents is 255". It's good to know that the
automated transfer of memos fields will not work at this time. I'm aware of
method one work around (the non automated approach). .. not to thrilled with
the creating of multple text boxes solution. Ernie