Export to excel has character limit

[ngan]

I have Access 2007. I have dbs saved in an earlier version and also in 2007.
When I try to export a query with a memo field to an excel (2007 or even
earlier version), the data in the memo field is truncated to the first 255
characters.

I read that this is a bug in Access 2000 (I guess is still a bug) and the
work around is to export the entire table or split the memo fields into
separate 255 character fields, export to excel and then concatenate the
fields back into one.

Is there any other way (automated) that I can export the query and keep the
data intact? Alot of users exports data from query into excel. I don't want
to ask them to do any extra work (most of the time, they don't know what to
do).

 

[Ken Snell MVP]

Use TransferSpreadsheet action in a macro or in VBA code. It will export all
of the memo field's contents.

 

[ngan]

Thanks for the suggestion. Is that the only solution?

One issue with that. Users run reports (queries when they don't need the
fancy formatting). They will then do export or email the query. Do I have
to create a separate button for them to "view the data" vs "export the data"
on the report menu form?

Can i run the vba code or macro while viewing the data in a query?

 

[Ken Snell MVP]

No, TransferSpreadsheet cannot be run from a query's datasheet view
directly. You'd have to run a macro manually to do that.

Otherwise, you can run the macro from a command button on a form.

The query to be exported, whether you run the macro manually or from a
command button, must be a saved query.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/