INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Troubleshooting

Export to Excel Truncation by Garridon
Posted: 19 Sep 02

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.

Back to Microsoft: Access Reports FAQ Index
Back to Microsoft: Access Reports Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close