By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,165 Members | 850 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,165 IT Pros & Developers. It's quick & easy.

When exporting a report or query to Excel how can I get a memo field over 255 charac

P: 11
I am exporting a report to Excel, but memo fields are being cut to 255 characters in length (as if they were text fileds). How do I get around this?.
Feb 25 '10 #1
Share this Question
Share on Google+
6 Replies


NeoPa
Expert Mod 15k+
P: 31,494
Instead of exporting the data directly, create a query that shows the data and uses functions to return no more than the amount of data that an Excel cell can handle.

Messy, but then you're using Memo fields so you've already limited your options.
Feb 25 '10 #2

P: 11
@NeoPa
I've checked, and an excel cell can handle more than 255 characters so I'm not sure why it cuts the data off at 255 characters. We need the field to contain more that 255 characters so I can't make it a text field? 255 characters is the largest text field you can hace isn't it? So I figure that somehow the export function is taking the memo field to be a txt field? Is this the case?
Feb 25 '10 #3

NeoPa
Expert Mod 15k+
P: 31,494
@Tennotrumps
Because it's a Memo field I think. Read my post.
@Tennotrumps
You're not following the logic well here. Text fields and Excel cells can both handle >255 chars. Text fields aren't the problem (hence my post).
@Tennotrumps
No.
@Tennotrumps
No.

I just started some tests to ensure I was giving you accurate information. I found that my Memo field data was not truncated at 255 chars at all. I don't know what's happening to you, but I had text strings in my spreadsheet reaching 2,427 characters. I'm using Access & Excel 2003. What are you using? Particularly, which export format are you using?
Feb 26 '10 #4

P: 11
I'm using Acsess 2007 and Excel 2003. I was wondering if I could change the memo field to a text field and if that would fix the problem. If there isn't a limit of 255 characters would that be the simplest way to fix it? I am using a report based on a query and then exporting this to excel. I will try exporting the query direct, however the report appears to work better for what we are doing.

Thanks for your help
Feb 26 '10 #5

NeoPa
Expert Mod 15k+
P: 31,494
It seems I should apologise. Text fields are indeed limited to 255 characters. I've been feeding you wrong information all this time.

Having said that, I suspect that the reason this is failing is that the controls (rather than the fields) are limiting the data to 255 characters. Controls are objects like TextBoxes, ComboBoxes & ListBoxes that you find on Form or Report objects.

What you may like to consider, if it's a report you're exporting from, is to have various hidden controls to allow the export of all the data, but not show when the report is displayed or printed. This is assuming that exporting the underlying query is not good for you due to things like positions on the page of various controls.

So for instance, the query - assuming a field called [MemoField] - might be changed to :
Expand|Select|Wrap|Line Numbers
  1. SELECT ...,
  2.        [MemoField],
  3.        Mid([MemoField],1,255) AS [MF1],
  4.        Mid([MemoField],256,255) AS [MF2],
  5.        Mid([MemoField],511,255) AS [MF3],
  6.        ...
  7. FROM   ...
[MF1], [MF2] & [MF3] (and any others) would be 'shown' in hidden controls on the report. These should be included in the export, but if they are not, then you may need to consider making them visible, but put them behind something else on the report so they don't display.
Feb 26 '10 #6

P: 11
I've retired simply exporting teh query and thedata goies through ok that way so I think I'll just rework teh query a little so it exports in teh format we need. That is by far the simplest way. I'm afraid your instructions above are a bit over my head - though I'll keep them in mind for when a I have a bit more time.

Many thanks
Feb 28 '10 #7

Post your reply

Sign in to post your reply or Sign up for a free account.