Export to Excel Truncation

If you've tried exporting a report to Excel, you may have found that it cuts off the text. The reason is that when you export to Excel, Access exports as Excel 95, which can't handle the bigger fields. Even if you select a newer version of Excel, it will still export as 95. Exporting a table or query will NOT make a difference.

This is how to work around the problem:

Create a report for export. Put an unbound field on the report. Insert the following into the unbound field:

=Mid([txtFieldName],1,127)

The numbers refer to the start and end point of the characters (Microsoft says that it should truncate after 255 characters; however, it may also truncate after 127 characters).

Repeat the above process until all the characters are accounted for. The next field, for instance would be 128, 254.

After creating all the fields to split apart the text field, create a blank unbound field. This will export as a blank column in Excel.

Export the report.

In the blank field, insert the following formula:

=Concatenate(E2,F2)

...substituting in the appropriate cell numbers. Concatenate puts it back together again. You may want to set up a macro to automate this part of the process. Once you concatenate, the cells cannot be edited.